Leave cell blank if SUM = "0"

[tomvh444]

I am using a simple SUM formula eg: E4=SUM(B4:D4) Want I want is that if B4,C4,and D4 are all 0 then I want E4 to be blank not "0".

[protonLeah]

if b4,c4,and d4 are all 0 then

Please Login or Register  to view this content.

[nimrod]

=if(and(b4=0,c4=0,d4=0),"",sum(b4:d4))

This method makes sure "each" cell has a value of Zero and NOT that they Sum Zero. In other words Sum(-1,+2,-1) will return a value of Zero while Sum(0,0,0) will return a value of "".

[tomvh444]

I am using a simple SUM formula eg: E4=SUM(B4:D4) Want I want is that if B4,C4,and D4 are all 0 then I want E4 to be blank not "0".

Thank you for the formula. E4 is now blank when SUM(B4:D4) is "0". I do however, still need some help. I also have in H4 this formula: =E4-F4-G4. I now get #VALUE! in H4 when E4 is blank. If E4 has a value in it then H4 will show the value correctly of E4-F4-G4. I would like to have H4 appear blank when E4 appears blank. Again thanking you for your help.

[brokenbiscuits]

The #VALUE error shouldn't appear, unless when typing the formula you have used " " (including a space) rather than "". Removing the sapce between the quote marks should stop the errors.

However, to have H4 appear blank when E4 is blank, just use:

=if(E4="","",E4-F4-G4)

[zbor]

Or you can Custom format cell as:

Custom: 0,-0,,@

that won't show 0 as a result

[tomvh444]

Thank you again. You have given me the formula that does what I want. I will mark solved and recommend !