SUM in Excel
Last updated
SUM adds up numbers: a range of cells, single cells or values you type. For example, =SUM(B2:B7) returns the total of every number from B2 to B7. Separate several ranges with commas, as in =SUM(B2:B5,D2:D5).
SUM syntax
=SUM(number1, [number2], ...)
- number1
- The first thing to add: a range such as
B2:B7, a single cell, or a number. Text and empty cells in a range are ignored. - number2, ...
- Optional. More ranges, cells or numbers to add, separated by commas, up to 255 arguments.
Practice SUM here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Total a column of expenses
In B8, use SUM to add up the monthly expenses in B2:B7.
| Expense | Amount ($) |
| Office rent | 1,850.00 |
| Electricity | 214.60 |
| Internet | 59.90 |
| Office supplies | 132.45 |
| Cleaning | 280.00 |
| Insurance | 96.30 |
| Total |
How to use SUM, step by step
- Click the cell where you want the total, usually just below or to the right of your numbers, and type
=SUM(. - Select the cells to add with the mouse, for example B2:B7. Excel writes the range for you.
- To add another range, type a comma and select it, or hold Ctrl while you select.
- Close the parenthesis and press Enter. The AutoSum button (Σ) on the Home tab writes the same formula in one click.
SUM examples
Total a column
=SUM(B2:B13)
Adds the twelve monthly amounts in B2:B13.
Add two separate ranges
=SUM(B2:B5,D2:D5)
Adds columns B and D and skips column C in between.
Same cell across several sheets
=SUM(January:December!B8)
A 3D reference adds B8 on every sheet from January to December, for a yearly total.
Running total
=SUM($B$2:B2)
Copied down, the range grows by one row each time: $B$2 stays fixed, B2 becomes B3, B4 and so on.
Common SUM problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| Total too low, or 0 | Some numbers are stored as text (often after a copy from another program). SUM ignores them silently. | Select the cells and use the warning icon > Convert to Number, then check the total again. |
| #VALUE! | A cell inside the range already shows an error such as #VALUE!. SUM passes the error on. | Fix the error in the source cell, or find it with Go To Special > Formulas > Errors. |
| #REF! | The formula lists single cells (=SUM(B2,B3,B4)) and one of their rows or columns was deleted. | Use a range like B2:B4 instead: it adjusts by itself when rows are deleted. |
| #NAME? | The function name is misspelled (=SUMM) or written in another Excel language. | Type =SU and pick SUM from the list Excel suggests. |
SUM vs SUMIF
SUM adds everything in a range. To add only the rows that match a condition, such as the sales of one region, use SUMIF (one condition) or SUMIFS (several conditions) instead of filtering and adding by hand. To total a filtered list without the hidden rows, use SUBTOTAL.
Frequently asked questions
Why does my SUM return 0?
Either the numbers are stored as text, which SUM ignores, or the formula includes its own cell and Excel warns about a circular reference. Convert the text to numbers and make sure the range stops above the total.
How do I sum a whole column?
Use =SUM(B:B). Put the formula in another column, otherwise it includes itself and creates a circular reference.
How do I sum only the visible cells of a filtered list?
Use =SUBTOTAL(109,B2:B100). SUM always adds every row, including the ones hidden by a filter.
What is the difference between SUM and the + sign?
=B2+B3+B4 returns #VALUE! if one cell contains text, while =SUM(B2:B4) skips the text. SUM is also shorter and keeps working when you insert rows inside the range.
Keep practising
All 5 SUM exercises, with explanations and progress tracking.
Open the SUM practice set