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

Using the information found in this thread: https://www.excelforum.com/excel-gen...urn-value.html I modified the below formula to my needs, but the result is not resulting in a blank cell when the criteria doesn't have any matches.

The original suggestion:

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?

My modified version:

Please Login or Register  to view this content.

Thank you for your help.

You could also try the following:
=IFERROR(1/(1/SUMIFS($G3:$G26, $A3:$A26, TODAY(), $H3:$H26, "Admin")), " ")

All of this, of course, assumes you're not actually using Excel 2000 as your profile suggests. (If you were, SUMIFS wouldn't work at all.)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Originally Posted by leelnich

Hi eemiller1997- Are you sure SUMIFS is finding any matches?
=IF(COUNTIFS( $A3:$A26, TODAY(), $H3:$H26, "Admin")>0, SUMIFS($G3:$G26, $A3:$A26, TODAY(), $H3:$H26, "Admin"), " ")

This worked. My question is why? Why did first looking for today's date in col A first as well if there are any Admin's in Col H greater than nothing, before calculating the sum for col G for only admins on today's date with Admin?

It seems the formula is going through the data twice.

Is it a 2010 thing? I have 2016 at work and I'm finding there are some differences.

For some reason the forum won't allow me to make an attachment.


sumifs-question.jpg