SUMIFS Does Not Work With Blank Cells

[masouder]

I am trying to use SUMIFS to add amounts where the user can optionally enter filtering criteria for multiple columns and where some cells in filtered columns may be blank. The problem is that the total does not include rows where a criteria column is blank when no filtering criteria is entered. I have searched for a solution and implemented one that works in all cases except where no criteria is entered.

For example, the following scenario works: Joe has been entered as criteria for the Person column, nothing has been entered for the Region column and the SUMIFS function (highlighted) returns 8. This includes the two rows where Joe is the Person, regardless of what is in the Region column.

Book1
	B	C	D
3	Region		8
4	Person	Joe
5
6
7	Region	Person	Amount
8	West		1
9	East	Mary	2
10	West	Joe	3
11	East	Mary	4
12		Joe	5
Sheet1

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(SUMIFS(D8:D12,B8:B12,IF(LEN(C3)=0,{"*",""},C3),C8:C12,IF(LEN(C4)=0,{"*",""},C4)))

Similarly, if I enter West as the Region and leave Person blank then SUMIFS returns 4, which is also correct.

The problem occurs when I leave both Region and Person blank: SUMIFS returns 9, ignoring rows where Region or Person is blank. It should return 15. Any ideas how to make this work when all criteria are blank?

Book1
	B	C	D
3	Region		9
4	Person
5
6
7	Region	Person	Amount
8	West		1
9	East	Mary	2
10	West	Joe	3
11	East	Mary	4
12		Joe	5
Sheet1

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(SUMIFS(D8:D12,B8:B12,IF(LEN(C3)=0,{"*",""},C3),C8:C12,IF(LEN(C4)=0,{"*",""},C4)))

 

[Eric W]

Try:

=SUMPRODUCT(SIGN((B8:B12=C3)+(C3="")),SIGN((C8:C12=C4)+(C4="")),D8:D12)

 

[jasonb75]

Any ideas how to make this work when all criteria are blank?

You just need to change the comma in the second array to a semicolon and it will work fine.

=SUMPRODUCT(SUMIFS(D8:D12,B8:B12,IF(LEN(C3)=0,{"*",""},C3),C8:C12,IF(LEN(C4)=0,{"*";""},C4)))

Or you could just check for cells that are <>| (or any character that will never appear in the criteria range) which eliminates the need for checking both empty and not empty.

=SUMIFS(D8:D12,B8:B12,IF(C3="","<>|",C3),C8:C12,IF(C4="","<>|",C4))

 

[masouder]

Both options work, thank you. A couple of questions:

• Can you explain what it is about the semicolon versus the comma that makes it work?
• What if there is a third criteria column to consider? Do we use a semicolon for all criteria except the first?

 

[jasonb75]

Can you explain what it is about the semicolon versus the comma that makes it work?

Commas create a horizontal array, semicolons create a vertical array.
If you have 2 arrays with the same orientation then the formula sees 2 pairs of parallel criteria (first element in each array is one pair, second element in each array is another pair).
If you have opposite orientation then it sees 4 pairs in the form of a matrix (meaning that it also pairs the first element of each array to the second in the other) i.e. it uses all possible combinations. If you had 3 elements in each array that would give 9 combinations, 4 elements would give 16 and so on.

What if there is a third criteria column to consider? Do we use a semicolon for all criteria except the first?

You can only use 2 arrays like this. Any more criteria either need to be single values (not arrays) or for more than 2 arrays different methods need to be used. Something similar to @Eric W's suggestion would be a starting point, although the most efficient method may vary depending on exact requirements.

 

[Eric W]

Right, with my formula you can add as many conditions/columns as you want. If you have the same basic layout, you can even update it so you don't need to add conditions, just use a MMULT and all you need to do is change the ranges. But honestly, that leads to a complicated formula. If you can make my formula work, great. If you have additional requirements, it would probably be better to post your actual situation and we can review it. As Jason said, many times we can look at the specifics of your sheet and come up with something targeted for it.

 

[masouder]

Thank you both for the detailed replies. Actually, I like the solution what includes the "<>|" criteria best. It is simple and works with just the SUMIFS function. I tested it with a third criteria column and it worked perfectly. I still have to dissect Eric's approach to understand it, but that one looks very clean as well.

Again, thanks for the replies.