Excel isn't calculating the SUM of cells

Question

I'm trying to use a very simple formula which is =SUM(B9:B11) however the cell doesn't compute for some reason.

I've used Excel for years and have never had this problem. Any idea why it may be failing to update the SUM?

I'm using Excel 2007 on Windows 7 Pro. I've opened the spreadsheet on multiple different machines with the same results so it appears to be an issue with the spreadsheet itself and not Excel or the computer.

Additional Note: If I recreate the =SUM formula it will recompute the total. However, if I change one of the number it still doesn't auto-recalculate.

Also, if I press F9 the SUM will recalculate being manually forced to.

Answer 1

Also do you have formula calculation set to Manual?

Answer 2

Even if the data cells B9-B11 have numbers in them, one or more of them may have their data type set as text instead of a number. I've done this more times than I can count!

DigDB has a quick how to and screenshots to change the type, try that and see:

http://www.digdb.com/excel_add_ins/convert_data_type_text_general/

Answer 3

you need =SUM(b9:b11), not just SUM(b9:b11)

Answer 4

Could one or more of B9:B11 be formatted as non-numeric?

Answer 5

Sounds like the calculation order / dependencies are broken, so it does not recognise when to recalc that cell by itself.

Try forcing Excel to rebuild the calculation dependency tree, by pressing Ctrl+Shift+Alt+F9 and let it recalculate the whole lot.