SUM Function in Excel: Add a Column the Right Way
How to use the SUM function in Excel: add a range, several ranges, AutoSum, sum a whole column, and the mistakes that make totals wrong without warning.
By Ayoub Farid · · 2 min read
SUM is the first function almost everyone learns, and it's still the most used. It adds numbers. But a surprising number of wrong totals in real spreadsheets come from small SUM mistakes, so it's worth learning properly.
The syntax
=SUM(number1, [number2], ...)Each argument can be a single cell, a range or a number. You'll mostly use one range.
Add a column
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 4,200 |
| 3 | Feb | 3,900 |
| 4 | Mar | 4,800 |
| 5 | Total |
In B5:
=SUM(B2:B4)Result: 12,900.
Add several ranges or cells
=SUM(B2:B4, D2:D4)
=SUM(B2, B4, B6)Commas separate the pieces. Excel adds them all.
AutoSum: the shortcut
Click the empty cell below your numbers and press Alt + = (Windows) or Cmd + Shift + T (Mac). Excel writes =SUM(...) with its best guess of the range. Always check the guess: it stops at the first blank cell.
Sum a whole column
=SUM(B:B)This adds every number in column B, so new rows are included automatically. Don't put this formula inside column B, or you'll get a circular reference.
SUM vs the + sign
=B2+B3+B4 gives the same answer as =SUM(B2:B4) here. Differences:
- SUM is shorter for long ranges.
- SUM ignores text in the range;
+returns#VALUE!if a cell holds text. - Inserting a row inside a SUM range extends it automatically. A chain of
+doesn't.
Mistakes that give the wrong total
Range one row short
=SUM(B2:B12) when data goes to B13. The total looks plausible, which is why it's dangerous. Click the formula and check that the coloured box covers every row.
Numbers stored as text
Imported data often has numbers stored as text (often left-aligned with a green triangle). SUM skips them silently. Convert them with Data > Text to Columns > Finish, or multiply by 1.
Hidden or filtered rows
SUM includes hidden rows. To total only visible rows after filtering, use =SUBTOTAL(9,B2:B13).
Including the total in itself
If the total sits in B14 and the range is B2:B14, you create a circular reference.
Conditional sums
To add only some rows (e.g. only "Coffee" sales), use SUMIF or SUMIFS. For a running total down a column, see running total.
Practise SUM
In Cellio's SUM lesson you tap the first cell, then the last, to build the range, then check the answer on a full year of sales. If the range is a row short, the feedback says so, with the number of cells you used versus the number in the data.
Frequently asked questions
How do I sum a column in Excel?
Click below the numbers and write =SUM(B2:B13), or press Alt+= for AutoSum.
Why is my SUM showing 0?
The numbers are probably stored as text, or the range points at the wrong cells.
How do I sum only visible cells?
Use =SUBTOTAL(9,range) or =AGGREGATE(9,5,range) after filtering.
Is SUM the same in Google Sheets?
Yes, =SUM(B2:B13) works identically. The AutoSum shortcut differs.