Hi all,
can anyone tell me how to sum cells without showing zero if cells are blank?
regards
Simon
Hi all,
can anyone tell me how to sum cells without showing zero if cells are blank?
regards
Simon
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
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
>
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
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
Hi Roger,
You can try this.
=IF(A2=0,"",SUM(A2:A20))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks