SUM function doesn't calculate with blank cells

[Guest]

the SUM function doesn't calculate if there are any blank cells in the array.

example: if i put in- sum(a1:a5) in cell a6, the return = "0". if i put in
value 1 in a1, the return is still "0"!

what's going on?

 

[Guest]

Press F9 to force Excel to recalculate.

 

[Guest]

Does the work CALCULATE appear on the status bar in the lower left hand
corner of the Excel window? If yes, press F9 to recalc your worksheet. To
turn calculation back to automatic, click TOOLS, select OPTIONS and click the
CALCULATION tab. Click the AUTOMATIC option button to reset it from manual
to auto.

 

[Guest]

Does the range A1:A5 contain numeric data? =SUM() only works on numbers, not
on text that looks like numbers. Try entering a simple formula in cell A1,
like "=1" (without the quotes). If the equals sign still shows after pressing
enter the cell is formatted as text.

If that is the case select the range A1:A5 (or perhaps the whole column
while you're at it) and select a numeric format for the range. You will still
have to re-enter the data though, since changing the format does not change
the data type.

Hope that helps,

TK

 

[RagDyer]

Another thought.

Could A1 to A5 be formatted as text?

Try this in A7:
=A1+A2+A3+A4+A5
And see if you get a result.

Sum() will not add text values.

Reformat the cells to General or Number, and re-enter the values.

 

[Dave Peterson]

You have lots of answers at your other thread.

the SUM function doesn't calculate if there are any blank cells in the array.

example: if i put in- sum(a1:a5) in cell a6, the return = "0". if i put in
value 1 in a1, the return is still "0"!

what's going on?