SUM

Arguments

• number1 - The first number or range to add.
• number2 (optional) - Additional numbers or ranges.

Examples

How SUM evaluates

SUM adds numbers. What makes it worth a reference page is that it does not add everything you hand it: the rules differ depending on whether a value arrives inside a range reference or as a value you typed directly into the formula. Inside a range, SUM looks at each cell and keeps only the ones that hold a real number. Text is skipped, TRUE and FALSE are skipped, empty cells are skipped. Typed directly as an argument, the same values are converted instead: =SUM("5", TRUE) is 6, because Excel assumes that if you wrote it into the formula yourself you meant it as a number.

That split is the source of the most common SUM complaint. A column imported from a CSV, a web page or an accounting system often holds numbers stored as text - the cells are left-aligned, and a green triangle may appear in the corner. SUM over that range returns 0 and gives no warning, because from its point of view the range contains no numbers at all. COUNT on the same range returns 0 too, which is the quickest way to confirm the diagnosis. The fix is to convert the data rather than to change the formula: select the column and run Data > Text to Columns > Finish, multiply the range by 1 with Paste Special, or add a helper column of =VALUE(A2).

SUM also behaves differently from the + operator when errors and text are involved. =A1+A2+A3 fails with #VALUE! if any of those cells holds text; =SUM(A1:A3) quietly ignores it. Both propagate a real error value: if any cell in the range contains #N/A or #DIV/0!, SUM returns that error, and you need SUM(IFERROR(...)) or AGGREGATE to step over it. For totals restricted by a condition, SUMIF and SUMIFS take over; SUM's only job is the unconditional total.

Text inside a range

Ignored entirely, including text that looks like a number. A range of "10", "20", "30" sums to 0, not 60.

Text typed as an argument

Coerced. =SUM("10", 5) is 15. If the text cannot be read as a number, =SUM("ten", 5) returns #VALUE!.

TRUE and FALSE

A boolean in a range contributes nothing. A boolean written into the formula counts as 1 or 0, so =SUM(TRUE, TRUE) is 2.

Blank cells

Skipped. They do not add zero and they do not affect anything, which is why SUM over a half-empty column is safe where averaging is not.

Errors

Any error value anywhere in the arguments becomes the result. The first one encountered wins; SUM has no error tolerance of its own.

Empty arguments and huge ranges

=SUM(A:A) is legal and ignores the header text along with every blank cell. =SUM() with no arguments is a syntax error, not zero.

Errors SUM returns

More examples

Compatibility

Does SUM Work in Google Sheets?

Yes. SUM uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.

In the ExcelTool editor

The ExcelTool editor implements SUM with one to any number of arguments and applies the same range-versus-argument split Excel does: values inside a referenced range count only when they are stored as numbers, while a scalar argument that is text is parsed and a scalar boolean counts as 1 or 0.

Its text parser accepts more than a bare numeral: leading and trailing spaces, a percent sign, thousands separators and a leading $, £, €, ¥ or ₹ are all understood, so =SUM("$1,200", 300) evaluates to 1500 rather than #VALUE!.

An error value found in any argument, whether a scalar or a cell inside a range, is returned immediately as the result of the whole SUM.

Calling SUM with no arguments at all returns #VALUE! in the editor, because the argument count is checked before the function runs.

You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.