The SUM function in Excel
SUM adds numbers. Give it a range like B2:B6 and it totals every number in it; give it several ranges or single values and it adds them all. It's usually the first function people learn, and the one they use most.
Text and empty cells in the range are simply skipped, so a header or a blank row doesn't break the total.
Syntax
=SUM(number1, [number2], …)
| Argument | What it means |
|---|---|
| number1 | The first number, cell or range to add, e.g. B2:B6. |
| number2, …optional | More numbers, cells or ranges, separated by commas. Up to 255. |
Examples
1. Total a column
| A | B | |
|---|---|---|
| 1 | Month | Expenses |
| 2 | Jan | 820 |
| 3 | Feb | 760 |
| 4 | Mar | 905 |
| 5 | Apr | 690 |
| 6 | May | 845 |
| 7 | Total | 4020 |
One range instead of =B2+B3+B4+B5+B6. Add a row inside the range later and the total still includes it.
2. Add several ranges at once
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Store | Online | All sales | |
| 2 | Jan | 1200 | 450 | 5070 | |
| 3 | Feb | 980 | 520 | ||
| 4 | Mar | 1310 | 610 |
Separate ranges with commas. Store (3,490) plus online (1,580) = 5,070. =SUM(B2:C4) gives the same result here, because the columns sit side by side.
3. Mix a range and a fixed number
| A | B | |
|---|---|---|
| 1 | Month | Expenses |
| 2 | Jan | 820 |
| 3 | Feb | 760 |
| 4 | Mar | 905 |
| 5 | Apr | 690 |
| 6 | May | 845 |
| 7 | Total + fee | 4170 |
The five months plus a one-time fee of 150. Better still: put the fee in its own cell and add that cell, so it's easy to change.
4. Running total
| A | B | C | |
|---|---|---|---|
| 1 | Month | Expenses | Running total |
| 2 | Jan | 820 | |
| 3 | Feb | 760 | |
| 4 | Mar | 905 | 2485 |
| 5 | Apr | 690 | |
| 6 | May | 845 |
Typed in C2 as =SUM($B$2:B2) and copied down. The $ keeps the start fixed while the end moves, so each row adds everything so far: by March, 2,485. The guide to cell references below explains how $ works.
Common mistakes
SUM returns 0 or too little. Some numbers are stored as text (often after importing data), and SUM skips text.
Fix: Look for numbers aligned to the left or with a green triangle. Convert them with Data › Text to Columns, or multiply by 1.
Adding cells one by one, e.g.
=B2+B3+B4+B5+B6. It works, but it breaks easily and doesn't grow with the data.Fix: Use a range:
=SUM(B2:B6).The total includes itself, e.g.
=SUM(B2:B7)typed in B7. Excel warns about a circular reference.Fix: End the range one row above the total.
Frequently asked questions
How do I sum a whole column?
Use the column alone: =SUM(B:B). Put the formula in a different column, or it will include itself.
Is there a shortcut for SUM?
Yes: select the cell below a column of numbers and press Alt and = together (AutoSum). Excel writes the SUM formula for you.
How do I add only some of the values?
Use SUMIF for one condition or SUMIFS for several, e.g. only the North region or only paid orders.
Related functions
- SUMIFSUMIF adds up the values that meet one condition, such as all sales of one region or all amounts above 100.
- SUMIFSSUMIFS adds up values that meet several conditions at once, such as paid orders from one region.
- AVERAGEAVERAGE returns the arithmetic mean of numbers or a range, skipping text and empty cells.