7

I am 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.

3
  • you need =SUM(b9:b11)
    – soandos
    Jul 11, 2011 at 14:10
  • I have =SUM...sorry, that was just a typo. Jul 11, 2011 at 14:13
  • I had the same problem, it was because there was a space (invisible to the eye) after each number, so even though the cells were formatted as number, and calculations set to 'Automatic', it still wasn't calculating. I used =left(A1, len(A1)-1) to remove the errant space
    – stevec
    Jul 19, 2020 at 9:31

10 Answers 10

9

Also do you have formula calculation set to Manual?

enter image description here

1
  • I found this under Excel Options > Formulas just before you posted the reply...however this was the problem so you get the answer! Thanks. Jul 11, 2011 at 14:25
4

This happened to me when I changed my computer's default language from English to French. In French, commas (,) are used instead of decimal points (.) in numbers, and vice versa. In Excel I was still writing numbers in the English format (3.42) when it was expecting a French format (3,42).

I fixed it by finding 'Change the date, time or number format' in the Start search box, clicking on Additional Settings, then changing the symbol for a decimal point to (.) instead of (,).

4

If a cell contains a circular reference, it will not autosum correctly (or at all). From the Formula tab, choose Error Checking>Circular Reference, trace it and then fix it.

2

I was having the same symptom. The problem turned out to be a Circular Reference within the numbers I was attempting to sum. I used the formula audit tool and had it point to the circular reference (which I had overlooked). Once that was corrected, the SUM function worked as normal.

1

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

1
  • it would simply be ignored if that was the case
    – AdamV
    Jul 15, 2011 at 8:07
1

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/

1

You can try using commas instead of dots as separators:

32,32 instead of 32.32

0

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

0

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.

0

For no good reason I can discern in my own case, the problem seemed to be the internal format of the time data itself, not the formatting of the function cell (and despite the fact I have formatted the entire column of numbers to various time formats and certain math functions like discrete addition yield the expected time based results)

Anyway, apply @timevalue to all data in the source time column and then paste the result(s) into a new column. It will look the same but the sum and subtotal functions will now work on the new column. Doesn't seem it should be necessary, but there it is.

Not the answer you're looking for? Browse other questions tagged .