# SUMIFS Formula With Empty Criteria Cells

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.

___________________________

=SUMPRODUCT(SUMIFS($D$2:$D2,$B$2:$B2,$B2,$C$2:$C2,IF(LEN($C2)>0,$C2,{“*”,””})))

Nice one, David, thanks!

How about?

=IF(C2=””,SUMIF(B$2:B2,B2,D$2:D2),SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,C2))

Thanks MF!