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

___________________________

Save

Save

### 5 Responses

1. David N says:

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

2. MF says: