Excel SUM Function: Add Up a Range of Cells
Adds up every number in the ranges you give it, ignoring text and blanks.
SUM is the first formula almost everyone learns and the one that appears in more spreadsheets than all the others combined. You give it cells and it adds the numbers in them. =SUM(B2:B6) totals a column; =SUM(B2, D2, F2) totals three scattered cells; =SUM(B2:B6, D2:D6) totals two ranges at once.
What makes it more useful than typing =B2+B3+B4 is that it tolerates whatever is in the way. Text is skipped, blanks are skipped, and inserting a row inside the range extends the total automatically. Adding cells with + does none of that — one text entry and the whole thing becomes #VALUE!.
The trap is not in the function but in the range. A total that excludes the last row because someone added it below the range is the single most common spreadsheet error there is, and it never announces itself.
Syntax
=SUM(number1, [number2], …)Arguments
number1Required- The first range or value to add. A range is the normal case: SUM(B2:B6).
number2, …Optional- More ranges or values, up to 255 of them. They do not need to be next to each other.
The example data
Headers in row 1, data in A2:D6. Note the blank in C4 and the text in C6.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Units | Unit price | Region |
| 2 | Cordless drill | 12 | 89.99 | North |
| 3 | Extension lead | 40 | 12.5 | South |
| 4 | Safety goggles | 8 | North | |
| 5 | Work gloves | 25 | 5.4 | South |
| 6 | Tool belt | 6 | n/a | North |
Worked examples
=SUM(B2:B6)Result: 91
Every unit count added: 12 + 40 + 8 + 25 + 6.
=SUM(C2:C6)Result: 107.89
The blank in C4 and the text "n/a" in C6 are both skipped rather than causing an error.
=SUM(B2:B3, B5:B6)Result: 83
Two separate ranges in one total, skipping row 4.
=SUM(B2:B6) * 2Result: 182
SUM returns a plain number, so it drops into any larger calculation without ceremony.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Total is too low, no error shown
Why it happens: Numbers stored as text are skipped silently — SUM ignores them exactly as it ignores labels.
How to fix it: Text numbers align left by default. Convert them with VALUE, or use Text to Columns on the whole column to force a re-parse.
New rows are not included
Why it happens: A row added directly below the range falls outside it. Excel extends ranges for rows inserted in the middle, not appended at the end.
How to fix it: Convert the data to a Table (Ctrl+T) and use structured references, which grow as rows are added.
#REF!
Why it happens: A column or row the range referred to was deleted.
How to fix it: Rewrite the range. This is why totals should sit outside the block of data they add, not inside it.
Circular reference warning
Why it happens: The SUM range includes the cell the SUM is in.
How to fix it: Stop the range one row short of the total cell.
Tips worth knowing
- Alt and = inserts an AutoSum over what Excel guesses is the range above. Always check the guess.
- SUM ignores text; + does not. =SUM(A2:A3) survives a stray label where =A2+A3 returns #VALUE!.
- To total everything except the subtotals, use SUBTOTAL(109, range), which skips other SUBTOTALs.
- SUM across sheets with =SUM(Jan:Dec!B2) — every sheet between the two named ones is included.
Frequently asked questions
Why is my SUM returning 0?
Almost always because the numbers are stored as text, which SUM skips. Check whether they sit left-aligned in their cells, and look for a small green triangle in the corner. Selecting the column and running Data → Text to Columns → Finish forces Excel to re-read them as numbers.
What is the difference between SUM and SUBTOTAL?
SUM adds everything in the range including rows hidden by a filter. SUBTOTAL(109, range) adds only the visible rows and ignores any nested SUBTOTALs, which is what you want at the bottom of a filtered list.
How do I sum only some of the rows?
SUM has no condition. Use SUMIF for one condition or SUMIFS for several — the whole point of that family is adding a subset rather than everything.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.
- Circular References: The Bonus Pool That Has to Know Its Own Answer Before It Can Give One
- Linked Workbooks: The Group Summary That Was 133,250.00 Short Because Three Links Never Updated
- SUMPRODUCT: Weighted Averages, Two-Condition Sums and the Average Unit Price That Was Out by 516,711.60
- Twelve Monthly Tabs, One Total: 3D References, INDIRECT and the 47,500 Nobody Booked