📎 This article includes 1 downloadable practice file ↓
In this article
SUM adds numbers, cell references and ranges. It ignores text and empty cells, which makes it the safest way to total a column.
Syntax
=SUM(number1, [number2], ...)| Argument | What it means |
|---|---|
number1 |
A number, cell or range to add. |
number2… |
Optional: up to 255 more numbers or ranges. |
Examples
Example 1
=SUM(B2:B13)Total of twelve monthly values.
Example 2
=SUM(B2:B13, D2:D13)Two separate ranges added together.
Example 3
=SUM(Sales[Amount])Total of a table column — keeps working as rows are added.
Power combo
=SUM(Jan:Dec!B5)A 3D reference: adds cell B5 on every sheet from Jan to Dec — a yearly total across monthly sheets.
Common errors and fixes
| You see | Why, and the fix |
|---|---|
Total looks too small |
Some numbers are stored as text (left-aligned, green triangle). Convert them with Data → Text to Columns → Finish. |
#VALUE! |
Usually from a formula like =A1+A2 with text. SUM ignores text, so switch to SUM. |
SUM in practice
Where you will use it
- Monthly totals
- Running balance
- Totals across sheets (3-D SUM)
Worked example
Running total: the start is locked with $, the end moves down.
| Day | Sales | Formula | Result |
|---|---|---|---|
| Mon | 18500 | =SUM($B$2:B2) | 18500 |
| Tue | 22100 | =SUM($B$2:B3) | 40600 |
| Wed | 19800 | =SUM($B$2:B4) | 60400 |
| Thu | 25600 | =SUM($B$2:B5) | 86000 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Text numbers silently skipped | Check COUNT vs COUNTA |
| Total row included in the range | Double counting; end the range above the total |
Related functions
SUMIF · SUMIFS · SUBTOTAL · SUMPRODUCT
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Math & rounding24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.