I recently learned about The 100-Day Project (#The100DayProject), in which you work on making something every day, for 100 days. Would Excel formulas (#100DaysOfXL) be considered an artistic project? Maybe not, but I had fun working on this SUMIFS formula with empty criteria cells!
SUMIFS Formula With Empty Criteria
Someone emailed to ask how they could ignore one criterion in a SUMIFS formula, if that cell is empty. Here is the original formula:
=SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,C2)
It sums all the values in column D, starting in row 2, and down to the current row, where:
- values in column B match B in the the current row,
- and values in column C match C in the the current row
See more SUMIFS examples on the Sum Cells page of my Contextures website.
Ignore Cell If Blank
How can we change the formula, so it ignores the criterion for C, if the current row has an empty cell in column C?
First, I set up a sample sheet, where I could do a bit of experimenting, and entered the original formula in column E
It’s interesting that SUMIFS returns a zero if there is an empty cell in column C.
Try an Empty String
For my first solution, I tried using an empty string as the criterion, if C was empty.
=SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,IF(C2=””,””,C2))
That created totals in the rows with blank cells, but it only added up the other blanks.
Try a Wildcard
Next, I tried using an asterisk wildcard as the criterion, if C was empty.
=SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,IF(C2=””,”*”,C2))
That created totals in the rows with blank cells, but it didn’t include the values from blank cells. Apparently the wildcard doesn’t recognize those.
Repeat for the Win
Finally, I decided to repeat the first criteria range and its criterion, if C was empty:
=SUMIFS(D$2:D2,B$2:B2,B2, IF(C2=””,B$2:B2,C$2:C2), IF(C2=””,B2,C2))
And that worked! As you can see in the screen shot below, it summed all the previous items that met the column B criterion, and also included the rows where C is empty.
Well, that was fun – even if it’s not artsy enough to post on Instagram!
Other SUMIFS Formula With Empty Criteria Solutions
There are probably other ways to solve this SUMIFS problem, so if you’d use something different, let me know in the comments.
___________________________
Try using single quote between double quotes: “‘” for the blank cells for the criteria.
Formula for the first row…
=SUMPRODUCT(SUMIFS($D$2:$D2,$B$2:$B2,$B2,$C$2:$C2,IF(LEN($C2)>0,$C2,{“*”,””})))
Hi,
It is ok, but how need change the line, if the “B” column also include empty cells or many columns are in the criteria of sumifs and those all include empty cells sometime?
I try this, but not work:
=SUMPRODUCT(SUMIFS($D$2:$D2,$B$2:$B2,IF(LEN($B2)>0,$B2,{“*”,””})),$C$2:$C2,IF(LEN($C2)>0,$C2,{“*”,””})))
Actually I change this “IF(LEN($C2)>0,$C2,{“*”,””}))” to IF($C2″”,$C2,{“*”,””})) it is shoter.
Hi, I came here with another problem. I have a SUMIFS looking at SAP accounts (i.e. 123 456 789) and I wanted to have SUMs based on several criteria – i.e. for some lines I add up based on Trading partner for some other lines I would just add all up without filtering on any Trading partner. And your wildcard * worked to sort-of skip a criteria as it adds up everything (blank or not). Thanks!