How to sum cells but not show zero if cells are blank?

[Simon Lloyd]

Hi all,

can anyone tell me how to sum cells without showing zero if cells are blank?

regards
Simon

[Ron Coderre]

Simon:

Perhaps something like this:

A21: =IF(COUNT(A1:A20)=0,"",SUM(A1:A20))

If there are NO numbers in A1:A20, the formula returns "" (effectively, a blank). Otherwise, it returns the total.

Does that help?

Regards,
Ron

[Paul B]

Simon, here is one way,

=IF(SUM(A1:A10)=0,"",SUM(A1:A10))
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all,
>
> can anyone tell me how to sum cells without showing zero if cells are
> blank?
>
> regards
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=549933
>

[Simon Lloyd]

Thanks for the speedy reply both!, it did help alot, but i have one other problem, i need the statement to be an or statement i.e =IF(SUM(P5:Q5)=0,"",SUM(R4-P5+Q5)) needs to be something like =IF(SUM(P4)=0,(IF(SUM(Q4)=0,"",SUM(R3-P4+Q4)))) but this returns a value FALSE, it needs to say if P4 is 0 then sum R3+Q4 or If Q4 is 0 then sum R3-P4 Or if there are values in both then R3-P4+Q4 and of course if there are no values in P4,Q4 then dont show zero!

Phew! its clear as mud to me hope you can sort the muddle!

regards,
Simon

[rogerwarr]

I want to go the other way

I am working on an existing spreadsheet which is showing a blank if the answer is zero, but I want to show the zero

Can any one help me with what to look for or do to correct or change it to show a zero

thank you
rog

[Rambo4711]

I want to go the other way

I am working on an existing spreadsheet which is showing a blank if the answer is zero, but I want to show the zero

Can any one help me with what to look for or do to correct or change it to show a zero

thank you
rog

I don't know how your formulas are written. Maybee it's as simple as the general settings for your worksheet i set not to show zero values (See Excel-alternatives - Advanced)

[siri_tvrs]

Hi Roger,

You can try this.

=IF(A2=0,"",SUM(A2:A20))