Cell to return blank not 0

[Darts via OfficeKB.com]

In cell b14 this is my formula - if these cells are blank i want the return
to be blank it is giving me a return of 0
=if(sum(b4:B13),"",sum(b4:B13))

 

[Forgone]

In cell b14 this is my formula - if these cells are blank i want the return
to be blank it is giving me a return of 0
=if(sum(b4:B13),"",sum(b4:B13))

This formula won't work as the if statement is not entirely correct.

If Sum(b4:B13) then
Null
Else
Sum(B4:B13)
End If

There's nothing to compare it against in the If clause..... it should
be something like

If Sum(B4:B13) = 0 Then
Null
Else
Sum(B4:B13)
End If

or

=IF(sum(b4:b13)=0,"",Sum(B4:B13))

or.... use custom number formats.....

Why not use a custom number format on the cell.
Something along the lines of this.... _-* #,##0_-;-* #,##0_-;_-* ""??
_-;_-@_-

Format > Cells > Custom


If you want decimal places then it would look like.....


_-* #,##0.00_-;-* #,##0.00_-;_-* ""??_-;_-@_-


If you want a dash (using the decimal places)


_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-


Try that.

 

[Fred Smith]

What do you mean by "these cells" -- all cells in the range b4:b13? If so,
try:

=if(count(b4:b13)>0,sum(b4:b13),"")

Regards,
Fred.

 

[Shane Devenshire]

Hi,

Try =IF(SUM(B4:B13),SUM(B4:B13),"")

This works because if SUM(B4:B13) returns 0 Excel considers it FALSE, for
any other number Excel considers it TRUE.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Forgone]

Try =IF(SUM(B4:B13),SUM(B4:B13),"")

This works because if SUM(B4:B13) returns 0 Excel considers it FALSE, for
any other number Excel considers it TRUE.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

I've never come across that one before, works great!

 

[AnotherNewGuy]

Just a quick thanks for that explanation. I was reading these thinking that
the first parameter doesn't return a logical value. Turns out I was wrong.

 

[MyVeryOwnSelf]

Try =IF(SUM(B4:B13),SUM(B4:B13),"")

I've never come across that one before, works great!

.... unless, of course, there are numbers in B4:B13 but the sum happens to
be zero by coincidence.

 

[MyVeryOwnSelf]

Try =IF(SUM(B4:B13),SUM(B4:B13),"")

I've never come across that one before, works great!

.... unless, of course, there are numbers in B4:B13 but the sum happens to
be zero by coincidence.