The Fixing SUM guide
A wrong SUM rarely shows an error. The usual causes are a total that was typed in, a row added below the range, and a grand total that adds the subtotals and the rows inside them. Click the total and look at the outlined range.
The checklist
| Cause | How to spot it | Fix |
|---|---|---|
| A total typed in as a number | The formula bar shows a number, not a formula | =SUM(B2:B4) |
| A row added under the range | The outlined range stops one row short | Extend it: B6:B8 |
| Subtotals inside the grand total | The grand total is about twice what it should be | Add the subtotals only: =B5+B9+B12 |
| Numbers stored as text | Some amounts sit on the left of the cell | Convert them, then SUM |
Why a new row is left out
A range grows when you insert a row inside it. A row added just below its last cell isn't inside, so =SUM(B6:B7) stays B6:B7 and the new row is never counted. An Excel table avoids this, because its totals cover every row in the table.
Subtotals and grand totals
=SUM(B2:B12) over a column with section totals in it adds every expense twice: once on its own row and once inside its section total. Add the section totals instead, or SUM only the expense rows: =SUM(B2:B4,B6:B8,B10:B11). SUBTOTAL(9, …) is another way: it skips other SUBTOTAL results in its range.
Check it against something
A quick check catches most of these: select the expense rows and read the Sum in the status bar, or compare with the bank statement. If the two disagree, one of the totals is wrong.
FAQ
- Why is my Excel SUM not adding up correctly?
- Check the range first: rows added below it are left out, and subtotals inside it are counted twice. Then look for numbers stored as text and totals that were typed in.
- Why does SUM leave out a row I added?
- The row was added below the last cell of the range, so the range didn't grow. Extend the range, or use an Excel table.
- How do I add subtotals without counting them twice?
- Add the subtotal cells, =B5+B9+B12, or build the subtotals with SUBTOTAL(9,…) and use SUBTOTAL for the grand total too, which skips them.
- Why does SUM show 0 or too little?
- Some numbers are probably stored as text, which SUM skips. They sit on the left of the cell, often with a green triangle.



































































