Excel: Sum Function

If you have ever built a monthly report, you know the drill: a column of numbers grows every week, and the total at the bottom needs to grow with it without you rewriting the formula each time. Adding cells one by one with a chain of plus signs works until someone deletes a row and the whole thing falls apart with a #REF! error.

This is exactly the gap the "Sum" function fills. It adds up everything you point it at, whether that is a single cell, a scattered handful of values, or an entire column, and it keeps working as your data changes. If you spend any time in Excel, the "Sum" function is probably the very first formula you learned, and it is worth understanding properly because a surprising number of totals go wrong for reasons that have nothing to do with your math.

Where to find it?

You type the "Sum" function straight into a cell. Start with an equals sign, type SUM, and Excel's formula AutoComplete will show the signature as you go. You can also reach it through the Formulas tab, "AutoSum" (the Greek sigma icon), or "Insert Function" if you prefer the guided dialog. It works the same way in Excel for Windows, Excel for Mac, and Excel for the web.

[Screenshot needed: A cell with the function being typed and Excel's formula AutoComplete tooltip showing the signature]

Let's see how to use it.

Usage

The syntax is short. You give the "Sum" function one or more numbers, cell references, or ranges, and it adds them all together.

=SUM(number1, [number2], ...)

Numbers are added the way you would expect.

Data in A1:A4
10, 20, 30, 40

=SUM(A1:A4)

will return

100

Dates and times get added too, because Excel stores them internally as serial numbers. The result is a real, calculable number, it just isn't one you can read as a date.

Data in A1:A2
01/01/2026, 02/01/2026

=SUM(A1:A2)

will return

92047

Text sitting inside a referenced range is ignored, which keeps a stray label from breaking the total.

Data in A1:A4
10, "apple", 20, (blank)

=SUM(A1:A4)

will return

30

Here is the part that catches people out. Text that looks like a number and logical values (TRUE/FALSE) behave differently depending on whether you type them directly into the formula or reference them from a cell. Typed directly, Excel converts them to numbers.

=SUM("5", 15, TRUE)

will return

21

The same values sitting in a referenced range keep their original type, and the "Sum" function skips them.

Data in A1:A3
"5", 15, TRUE

=SUM(A1:A3)

will return

15

An error value anywhere in the range is a different story: it does not get skipped; it takes over the entire result.

Data in A1:A3
10, #DIV/0!, 20

=SUM(A1:A3)

will return

#DIV/0!

The function family

The "Sum" function has several relatives built for more specific jobs. None of these have a dedicated article yet, so keep this table handy.

Non-intuitive behaviors

Text and TRUE/FALSE change meaning depending on where they sit

The distinction you saw in the Usage section is worth repeating on its own, because it is the single most common source of a "wrong" total. A value typed directly as an argument gets converted if it looks numeric. The same value sitting in a cell that the formula references does not, and is quietly skipped instead.

Dates and times add up without warning you

Because dates and times are stored as serial numbers, summing a range that includes them produces a real number, not an error, even though the result rarely means anything useful on its own. If a total looks unexpectedly large, check whether a date accidentally landed inside the summed range.

Limitations

A single error stops the whole total

If any cell in the summed range holds an error value, such as #VALUE!, #N/A, or #DIV/0!, the "Sum" function returns that same error instead of a number. It does not skip the bad cell the way it skips text; it stops and reports the problem.

Maximum of 255 arguments

You can pass up to 255 separate numbers, references, or ranges to a single "Sum" function. In practice you almost always pass one or two ranges, so this rarely matters, but it exists.

Blank cells and empty strings contribute nothing

A truly blank cell and a cell holding an empty string ("", often left behind by another formula) both behave as if they held no value at all. Neither raises an error, and neither shows up in the total, which is usually what you want. This is consistent, reproducible behavior, even though Excel's own documentation does not spell out the empty-string case specifically.

Localization

The argument separator changes with your regional and list separator settings. With some settings it is a comma, with others, such as Portugal, it is a semicolon.

// Comma-based regional settings
=SUM(A1:A10, C1:C10)

// Semicolon-based regional settings (for example, Portugal)
=SUM(A1:A10; C1:C10)

Troubleshooting Common Errors

Symptom: The "Sum" function returns 0 even though the column clearly has values. Cause: The values are stored as text rather than numbers, often after importing data from a CSV file or pasting from a web page. Solution: Check with =ISNUMBER(A1); if it returns FALSE, convert the column with Data, "Text to Columns", "Finish", or wrap the value in the "Value" function.

=VALUE(A1)

Symptom: The "Sum" function returns #VALUE!. Cause: One of the referenced cells already contains an error, and that error is propagating into your total. Solution: Find and fix the source error, or wrap the formula in the "Iferror" function so a bad cell does not take down the whole report.

=IFERROR(SUM(A1:A10), 0)

Symptom: Excel rejects the formula as you type it, or shows a syntax error. Cause: You typed a comma where your regional settings expect a semicolon (or the other way around). Solution: Use the list separator your Windows regional settings expect. Opening a workbook built with a different separator usually converts automatically with no action needed, but this can break down with a CSV import, a formula pasted as plain text, or a file shared through Excel for the web, so check the separator in Excel's Advanced options if a formula still shows an error after opening.

Recommendations

Here are some things to keep in mind.

Reference whole columns or named ranges

If your data keeps growing, point the "Sum" function at a whole column or a named range so the formula keeps working without edits later.

=SUM(A:A)

Prefer Sum over a chain of plus signs

=SUM(A2:A6) survives a deleted or inserted row. =A2+A3+A4+A5+A6 does not: delete one of the referenced cells and it breaks with a #REF! error. The "Sum" function also expands automatically when you insert a new row inside the range.

Always deal with errors

A single error value anywhere in the range takes over the whole result, so it is worth catching before it reaches a dashboard or a report a colleague depends on. Wrap the formula in the "Iferror" function or clean the source data so one bad cell does not erase an otherwise correct total.

=IFERROR(SUM(A1:A10), 0)

Final Thoughts

The "Sum" function looks simple, and most of the time it is, but the moment text, dates, or an error value slip into the range, the result stops being obvious. Remember that typed values and referenced values are not treated the same way, keep an eye out for errors that can take over the whole total, and reach for the "Sumif" or "Sumifs" function when you need a condition attached.

Sources

• The "Sum" function on Microsoft Support
• How to correct a #VALUE! error in AVERAGE or SUM functions
• Overview of formulas in Excel

Back to the Excel Function Reference

Photo by Julia Taubitz on Unsplash