Skip to content

Excel function · Math

Excel SUM function

SUM adds up the numbers, cells and ranges you give it: the total of a column, three months of sales, amounts in scattered cells. It is the most used function in Excel.

In Turkish Excel: TOPLA

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

English Excel=SUM(number1, [number2], ...)
Turkish Excel=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

ArgumentRequiredDescription
number1RequiredThe first number, cell or range to add: D2:D20, B5 or 1250.
number2OptionalOptional 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.

B7fx=SUM(B2:B6)
Total the monthly expenses: example table
AB
1ExpenseAmount
2Rent18,500.00
3Electricity2,340.60
4Internet649.90
5Stationery1,215.25
6Cleaning3,200.00
7Total25,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.

E2fx=SUM(B2:D2)
Row totals and a grand total: example table
ABCDEFGH
1ProductJanFebMarQuarterGrand Total
2Copper cable12,4009,80014,15036,35088,060
3Grounded socket5,3206,1105,87517,305
4Circuit breaker8,9007,4509,30025,650
5Cable duct2,7603,0152,9808,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.

D2fx=SUM(B2:B6)
A total that skips text and empty cells: example table
ABCD
1InvoicePaymentTotal
2INV-10014,200.0040,599.90
3INV-1002Cancelled
4INV-10039,999.90
5INV-1004
6INV-100526,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

Type this in Nexell

Add a Total row that sums each month and a column with each product's three-month total

Convert to table

Bring 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

All math functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free