Formulas not data 0

[Ravi Sandhu]

Hi guys

I want to somehow create a formula, which give me the following results...

I want it to SUM a serious of cells

e.g. SUM(A1:A5)

However, if there are no results in cells A1 to A5, I don't want the SUM
cell to display a 0

I want for it to stay blank.

Please advise

thanks

 

[Bob Phillips]

Hi Ravi,

IF(SUM(A1:A5)=0,"",SUM(A1:A5)

or else you can turn off displaying zeroes in Tools>Options Edit tab,
although this will apply to the whole workbook.

 

[Norman Harker]

Hi Bob!

What you meant was:

=IF(SUM(A1:A5)=0,"",SUM(A1:A5))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Saturday 2nd August: Andorra (Andorra La
Vella), Bosnia – Herzegovina (Ilindan), Costa Rica (Virgin of the
Angels Day), Macedonia (Ilinden / St. Elijah’s Day), St. Kitts & Nevis
(Emancipation Day), Yugoslavia (Ilinden / St. Elijah’s Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

Hi guys

I want to somehow create a formula, which give me the following results...

I want it to SUM a serious of cells

e.g. SUM(A1:A5)

However, if there are no results in cells A1 to A5, I don't want the SUM
cell to display a 0

I want for it to stay blank.

Please advise

thanks

In addition to what others have posted, you could keep your formula the same
and use a custom format for that cell:

Format/Cells/Number/Custom/Type: #,##0.00;-#,##0.00;;

That's an example of formatting with commas and two decimal places. In the
format string, semicolons ( are used as separators, and the third entry is
for 0 values. Since there is nothing there, a zero is displayed as a blank,
although the value is still in the cell.


--ron

 

[Bob Phillips]

Confidence over caution! Thought I couldn't get a simple formula like that
wrong, that will teach me!

Thanks for ensuring the OP gets a correct answer.