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.

original SUMIFS Formula With Empty Criteria

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.

SUMIFS formula with empty string

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.

SUMIFS formula with wildcard

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!

sumifsignoreblank04

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

You may also like...

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *