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:
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.
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.
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.