The SUM function in Excel
SUM adds up numbers: a column of sales, a row of monthly costs, or several separate ranges at once. It's usually the first function anyone learns, and it still does most of the work in budgets, invoices and reports. SUM skips text and empty cells, so you can point it at a range with a few gaps without errors.
Syntax
=SUM(number1, [number2], ...)
| number1 | The first number, cell or range to add, like B2:B6. |
|---|---|
| number2, ... | Optional. More numbers or ranges to include, separated by commas. Up to 255 in total. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
Finish the invoice. In D7, use SUM to add up the amounts in D2:D6.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Amount | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | Total | ||||||
| 8 | |||||||
| 9 | |||||||
| 10 |
Tip: Give SUM the range of amounts, from the first line item to the last.
Examples
=SUM(B2:B6)→ 5275Adds the January column: total spending for the month.
=SUM(B4:D4)→ 2515Adds across a row, giving total Travel spend for the quarter.
=SUM(B3:D3,B6:D6)→ 5125Two separate ranges in one formula: Software plus Marketing for all three months.
Example data
| 1 | Category | Jan | Feb | Mar |
|---|---|---|---|---|
| 2 | Rent | 2400 | 2400 | 2400 |
| 3 | Software | 310 | 295 | 340 |
| 4 | Travel | 860 | 1240 | 415 |
| 5 | Utilities | 205 | 188 | 176 |
| 6 | Marketing | 1500 | 950 | 1730 |
Common mistakes
Numbers stored as text
Values imported from other systems are sometimes text, often left-aligned with a green triangle. SUM ignores them silently. Convert them with VALUE, or use Text to Columns to turn them back into numbers.
Range misses new rows
If you add rows below
B2:B6, the formula won't include them. Insert rows inside the range, or format the data as a Table so the total grows with it.Totals counted twice
Including a subtotal row inside the range doubles those numbers. Keep subtotals out of the range, or move them to a separate column.
Chaining cells with +
=B2+B3+B4+B5is easy to break and fails with #VALUE! if any cell holds text.=SUM(B2:B5)is shorter and skips text.
FAQ
Why does my SUM formula return 0?+
Most often the numbers are stored as text. Check whether they're left-aligned, then convert them to real numbers. Also make sure calculation is set to Automatic.
How do I sum an entire column?+
Use a whole-column reference like =SUM(B:B). Put the formula in a different column so it doesn't include itself.
How do I sum only cells that meet a condition?+
Use SUMIF for one condition, like a single category, or SUMIFS for several conditions at once.
Lesson
Master SUM step by step: SUM and AutoSum
Add up a column of numbers with SUM, then let AutoSum write the formula for you.