Excel Displaying Zero For Cell With Formula

[PaddyP]

Please help.

In Cell D1 I have a formula SUM(A1:C1)

If there are no entries I want D1 to be blank however if I enter 0,0,0 in cells A1:C1 then I want the formula to return 0 in D1.

What is the easiest way to do this?

Thanks

[JohnTopley]

Try

=IF(COUNTIF(A1:C1,0)=0,"",SUM(A1:C1))

[MarvinP]

Hi PaddyP,

Try this is D1

=IF(LEN(A1&B1&C1)=0,"",SUM(A1:C1))

[PaddyP]

Wow excellent. Thanks!

[Tony Valko]

Here's another one...

=IF(COUNT(A1:C1),SUM(A1:C1),"")

[PaddyP]

Just an FYI the first suggestion does not work if you have a value other than zero in all three cells.

2nd and 3rd formulas work great!

Thanks again to everyone for your help. Solved!

[Larry.LeBlanc@O]

Can't you format D1 to not show 0? I think the custom format is 0;-0;;

[Tony Valko]

You're welcome. Thanks for the feedback!