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

1
votes

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.

4
excelexcel-formula
=SUM(SUMIF(A1:A7,{"A",""},B1:B7)) and similar to sumifs – Dang D. Khanh
You can fill the blank cells with single quotation marks? – Naresh
@TheGridLock Can we have one formula where column has not blank & blank sum if condition with not blank and blank criteria? – ROn
Can you upload an image that describes what you want? – Dang D. Khanh
@TheGridLock Uploaded image 3. Need a common formula that can be used through Column F. – ROn

4 Answers

1
votes

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.

1
votes

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

0
votes

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

enter image description here

0
votes

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