SUMIFS: If no criteria match, leave cell blank, else return value

**KCchristoph** · 03-26-2013, 12:48 PM

Here is a potentially easier solution, where everything in [] gets replaced by your specific ranges and criteria cell names:

=IF(COUNTIF([criteria range], [criteria])>0, SUMIFS([sum range], [criteria range], [criteria]), "")

example: =IF(COUNTIF(H:H, A2)>0, SUMIFS(I:I, H:H, A2), "")

This will give you back a blank whenever there isn't a data match to sum, and will help you avoid false zeros.

The logic here is IF count finds at least one match, THEN add up the items with a match, ELSE return a blank.

**eemiller1997** · 07-02-2017, 10:19 AM

Originally Posted by KCchristoph

=IF(COUNTIF([criteria range], [criteria])>0, SUMIFS([sum range], [criteria range], [criteria]), "")

example: =IF(COUNTIF(H:H, A2)>0, SUMIFS(I:I, H:H, A2), "")

This will give you back a blank whenever there isn't a data match to sum,...

While I am getting the correct value when there is data that matches the criteria, I am still getting a zero when there isn't anything to sum. Where am I going wrong in the formula?

=IF(COUNTIF($G3:$G27, ">0"), SUMIFS($G3:$G26, $A3:$A26, TODAY(), $H3:$H26, "Admin"), " ")

Thank you for your help.

SUMIFS: If no criteria match, leave cell blank, else return value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: SUMIFS: If no criteria match, leave cell blank, else return value

Re: SUMIFS: If no criteria match, leave cell blank, else return value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions