Excel track · Exercise 1 of 86
SUM: total a column
Total a column with one function instead of typing every cell.
Beginner
Q1 revenue by region
Read-only data · answer in B5
In B5, total Q1 revenue for all three regions with one function.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Q1 | Q2 |
| 2 | North | 1200 | 900 |
| 3 | South | 1450 | 1100 |
| 4 | West | 930 | 700 |
| 5 | Total Q1 |
Reference answer
What it does
SUM adds the numbers in the range you give it. Any text or blank cell inside that range is stepped over rather than counted. =SUM(B2:B4) covers 1200, 1450 and 930 and returns 3580. Change South to 1500 and the total follows.
The failure runs the other way. A number stored as text looks like a number but SUM skips it, so the total comes out low and the cells look fine. A region missing from a total is usually a left-aligned figure in the column.
Point at the range rather than at three cells. 1200+1450+930 breaks when a row is inserted, while B2:B4 grows to take the new row in.
Syntax
=SUM(number1, [number2], ...)Worked example
On Q1 revenue by region, =SUM(B2:B4) returns 3580.
=SUM(B2:B4)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | A cell in the range holds text where SUM expected a number, usually the heading or the Region column in place of the Q1 figures. |
#NAME? | The function name is misspelled as SUMM or SU. Excel returns #NAME? for a name it does not recognise. |
Out of range | SUM(B2:B5) would reach the label in B5 as well. A range that runs one cell long picks up the total itself and doubles it. |
At work
Every monthly close: one row per branch, one column per period, and a total at the bottom that updates itself.