Formulas not data 0

  • Thread starter Thread starter Ravi Sandhu
  • Start date Start date
R

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
 
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.
 
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.
 
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
Click to expand...

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
 
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.
 
Back
Top