return blank

[John]

Hi,

I am using sumif functions who return "zero" if criteria
is not found. How can I let the function return a blank
instead of "0"?


Thanks

 

[Frank Kabel]

Hi John
try
=IF(SUMIF(...),SUMIF(...),"")

Note: this works only if you sum positive values. If your range could
also contain negative values a zero as result could be a valid result.

 

[Andy B]

Hi

You could use custom number formatting (like General;General; ;General), or
uncheck Tools / Options / View Zero values

 

[JE McGimpsey]

One way:


=IF(COUNTIF(A:A,">0"),SUMIF(A:A,">0",B:B),"")

 

[john]

Hi Frank

Thanks for your reply.
What is the diff. betw.
=IF(SUMIF(...),SUMIF(...),"") and >=IF(SUMIF(...)
=0,"",SUMIF(...))?

Regards,

 

[Frank Kabel]

Hi John
the second formula uses the fact, that TRUE is represented by 1 (or

=1) and FALSE by 0.

So the formula
=IF(SUMIF(....),SUMIF(...),"")
is a shortcut of
=IF(SUMIF(...)>0,.....)