If, say, =SUM(I2:I10) totals "0", how to change formula to put a "0" in I11?

  • Thread starter Thread starter StargateFanFromWork
  • Start date Start date
S

StargateFanFromWork

I know this is going to be simple but everything I've not got anything to
work. How can we change formula, pls, to have "=SUM(I2:I10)" show up as "0"
in I11 if there aren't any values anywhere in I2 to I10?

Thanks. :oD
 
If there are no values to sum the formula should already be returning 0.
What result are you getting?

Biff
 
If there aren't any values in I2:I110 then that formula should return zero,
what does it returns?
 
Your formula entered in I11 already does that if no values in I2:I10.

Re-think the question and post back.


Gord Dibben MS Excel MVP
 
T. Valko said:
If there are no values to sum the formula should already be returning 0.
What result are you getting?
Click to expand...

Nothing. The cells are blank that don't have any input. I had the cells
formatted as numbers with no decimal places but the numbers were squashed to
the right so I used a custom format of ##" " to get a right indent. In
any case, both are blank when there is nothing to sum in rows 2 to 10.

Thanks.
 
Gord Dibben said:
Your formula entered in I11 already does that if no values in I2:I10.
Click to expand...

Of course, but it doesn't show anything. If there are "0" values, we need
it to say "0" said:
Re-think the question and post back.
Click to expand...

No need. The question stands as is. Thanks.
 
It's thanks to your custom format, try something like this instead

##" ";-##" ";0" "
 
Peo Sjoblom said:
It's thanks to your custom format, try something like this instead

##" ";-##" ";0" "
Click to expand...

That's very kewl. That seems to work works perfectly, thanks! :oD
 
As a test, why don't you open a brand new, blank WB, and enter your formula
in I11?

You'll see that you DO get a zero ( 0 )!

So, you're intelligent enough to realize that it's your formatting that's
creating your problem ... aren't you?

Change your custom format to
0" "

And you should get what you're after.
 
StargateFanFromWork said:
I know this is going to be simple but everything I've not got anything to
work. How can we change formula, pls, to have "=SUM(I2:I10)" show up as "0"
in I11 if there aren't any values anywhere in I2 to I10?

Thanks. :oD
Click to expand...
Make sure you have "Zero values" checked (v) in Tools->Options
 
Back
Top