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

[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. D

 

[T. Valko]

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

Biff

 

[Peo Sjoblom]

If there aren't any values in I2:I110 then that formula should return zero,
what does it returns?

 

[Gord Dibben]

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

 

[StargateFanFromWork]

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

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.

 

[StargateFanFromWork]

Your formula entered in I11 already does that if no values in I2:I10.

Of course, but it doesn't show anything. If there are "0" values, we need

Re-think the question and post back.

No need. The question stands as is. Thanks.

 

[Peo Sjoblom]

It's thanks to your custom format, try something like this instead

##" ";-##" ";0" "

 

[StargateFanFromWork]

It's thanks to your custom format, try something like this instead

##" ";-##" ";0" "

That's very kewl. That seems to work works perfectly, thanks! D

 

[Ragdyer]

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.

 

[Guest]

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. D

Make sure you have "Zero values" checked (v) in Tools->Options

 

[Gord Dibben]

Maybe some more details then.

As is, not complete.


Gord