What it does
You can give SUM a range (=SUM(D2:D20)), several ranges (=SUM(B2:B10,D2:D10)) or single cells and numbers; it returns the total of all of them. The quickest way to put a total under a column is to press Alt and = together: Excel writes the AutoSum formula and picks the range above for you.
Text, empty cells and TRUE/FALSE values in a range are not added. That is usually what you want, but numbers that are stored as text are skipped too: a "1250" imported from another system quietly makes the total smaller. If the range contains an error such as #N/A, SUM returns an error as well.
To add values that meet a condition, use SUMIF and SUMIFS; to leave filtered or hidden rows out, use SUBTOTAL; to multiply two columns row by row and add the results, use SUMPRODUCT.
Syntax
=SUM(number1, [number2], ...)=TOPLA(sayı1; [sayı2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number1 | Required | The first number, cell or range to add: D2:D20, B5 or 1250. |
number2 | Optional | Optional further numbers, cells or ranges, up to 255 arguments in total. |
Examples
1Total the monthly expenses
The expense items are in B2:B6. The total goes in B7, right under the list, so the range ends one row above the total cell.
=SUM(B2:B6)| A | B | |
|---|---|---|
| 1 | Expense | Amount |
| 2 | Rent | 18,500.00 |
| 3 | Electricity | 2,340.60 |
| 4 | Internet | 649.90 |
| 5 | Stationery | 1,215.25 |
| 6 | Cleaning | 3,200.00 |
| 7 | Total | 25,905.75 |
The five items add up to 25,905.75. Change any amount and the total follows.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Row totals and a grand total
Three months of sales for each product sit side by side. Column E adds them row by row, and H2 adds the whole table with one range.
=SUM(B2:D2)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Product | Jan | Feb | Mar | Quarter | Grand Total | ||
| 2 | Copper cable | 12,400 | 9,800 | 14,150 | 36,350 | 88,060 | ||
| 3 | Grounded socket | 5,320 | 6,110 | 5,875 | 17,305 | |||
| 4 | Circuit breaker | 8,900 | 7,450 | 9,300 | 25,650 | |||
| 5 | Cable duct | 2,760 | 3,015 | 2,980 | 8,755 |
- E2 (filled down to E5)
=SUM(B2:D2) - H2
=SUM(B2:D5)
The copper cable's quarter total is 36,350; the grand total of all four products is 88,060. Adding up column E gives the same result.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3A total that skips text and empty cells
In the payment list, cancelled invoices say "Cancelled" and one row is empty. SUM skips those cells without an error.
=SUM(B2:B6)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Payment | Total | |
| 2 | INV-1001 | 4,200.00 | 40,599.90 | |
| 3 | INV-1002 | Cancelled | ||
| 4 | INV-1003 | 9,999.90 | ||
| 5 | INV-1004 | |||
| 6 | INV-1005 | 26,400.00 |
Only the three numbers are added: 40,599.90. A number stored as text would be skipped just as quietly, so check that imported amounts really are numbers.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Total too small
Cause: Some numbers are stored as text; a green triangle appears in the corner of the cell and SUM skips them.
Fix: Select the cells and choose Convert to Number from the warning icon, or use VALUE in a helper column.
- #N/A
Cause: The range contains a cell with an error (#N/A, #VALUE! and so on), and SUM passes it on.
Fix: Fix the source of the error, or use AGGREGATE, which can skip errors:
=AGGREGATE(9,6,D2:D20).- Circular reference
Cause: The total cell sits inside its own range:
=SUM(D2:D21)typed in D21.Fix: End the range one row above the total:
=SUM(D2:D20).
Good to know
- To see a total without writing a formula, select the cells; the status bar at the bottom of Excel shows the sum, average and count.
- You can add a whole column with
=SUM(D:D), as long as the total cell is not in that column. - To add only the visible rows of a filtered list, use SUBTOTAL instead of SUM.
In Nexell
Get Nexell to do it from a sentence
Add a Total row that sums each month and a column with each product's three-month total
Convert to tableBring your table into the tool and type this. Nexell adds the total row and column with real Excel formulas and shows them in the preview first; in the downloaded file, the totals change when you change an amount.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I sum a column in Excel?
Go to the first empty cell under the column, press Alt and = together, then press Enter. Or type the formula yourself: =SUM(D2:D20).
Why does SUM return 0?
Most likely the numbers are stored as text. Click the green triangle in the corner of the cells and choose Convert to Number, or convert them with VALUE in a helper column and sum that column.
Related functions
- SUMIFETOPLA · Adds the cells that meet a condition.
- SUMIFSÇOKETOPLA · Adds the cells that meet multiple conditions.
- SUBTOTALALTTOPLAM · Subtotal with a chosen function (9 = sum, 1 = average…).
- SUMPRODUCTTOPLA.ÇARPIM · Multiplies matching items of arrays and sums them; the classic conditional sum: SUMPRODUCT((City="Ankara")*Amount).
- AVERAGEORTALAMA · Arithmetic mean.