Fixing a broken total

1 / 4

Look at B5: it sits on the left of the cell. It's a number stored as text, and SUM skips text, so E1 is too small.

keyboard Typing formulas is easiest on a computer with a keyboard.

text

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How SUM and SUMIF works

Why can a total be wrong with no error?

Excel only adds up what you point it at. If the range or the data is wrong, you get a confident, wrong number. Three causes cover most broken totals.
1. Does the range cover every row?

Click the total and look at the coloured outline. Rows added under the range, or a range typed by hand, are left out. Fix it with a range that covers every row, or turn the list into an Excel table (Ctrl+T) so totals grow with it.
2. Are any numbers stored as text?

Text numbers sit on the left of the cell and SUM skips them. =SUM(VALUE(B2:B9)) counts them without touching the data, or fix the column itself with VALUE in a helper column.
3. Is anything counted twice?

A column with subtotals in it adds each item twice if you SUM the lot. Skip the subtotal rows by their label:
=SUMIF(A2:A11,"<>*Subtotal",B2:B11)
Or add up only the subtotals. Excel's SUBTOTAL(9,...) also ignores other SUBTOTAL formulas in its range.
How do I check a total?

Select the cells: the status bar at the bottom of Excel (and of the grid here) shows their Sum and Count. If Count is higher than the numbers you see, some cells hold text.

Know someone fighting with a spreadsheet? Send them this page.