CODEWITHSUNDEEP
excelexcel-formula
ROn
ROn

Reputation: 139

SUMIFS should consider both blank & not blank values.with single formula

Sheet1

Hello All, Below is the situation where Product Column is having 2 values:"A" & blank, where product is not know. I'm trying to calculate how total qty of Product A & unknown product.

I'm using SUMIFS condition, but formula doesn't consider blank cells (unknown product). How to consider blank cells, so that total qty would be "11" for unknown product.

Formula used =SUMIFS(B:B,A:A,A:A)

sheet3 I a common formula that can be used through column D or E.

Upvotes: 2

Views: 1649

Answers (4)

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

This works well, please try it:

  • $D$1:$D$2&"" as i understand it will fill a vbnullstring value type instead of empty cell, and it is a condition for countif to count cells with value = ""

.

=SUMIF($A$1:$A$7,INDEX($D$1:$D$7&"",ROW(D1)),$B$1:$B$7)

enter image description here

Upvotes: 1

Henrique Branco
Henrique Branco

Reputation: 1940

The following formula solves your problem:

=IF(ISBLANK(E2),SUMIFS(B:B,A:A,""),SUMIFS(B:B,A:A,E2))

I reproduced it with your data and it returned 11 for the unknown products.

Upvotes: 1

bosco_yip
bosco_yip

Reputation: 3802

In "Criteria" column D2 enter : "A" and D3 enter : =""

then,

In "Total Qty" column E2, enter formula and copied down to E3 :

=SUMIF(A:A,D2,B:B)

enter image description here

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8375

I went for summing for "A" and "<>A" ie not "A", like so:

enter image description here

Upvotes: 0

Related Questions