The Excel SUM Function

Function Description

The Excel SUM function adds together a supplied set of numbers and returns the sum of these values.

The syntax of the function is:

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

where the number arguments are a set of numbers (or arrays of numbers) that you want to find the sum of.

In current versions of Excel (Excel 2007 and later), you can enter up to 255 number arguments to the Excel Sum function, but in Excel 2003, the function can only handle up to 30 arguments. However, each argument can consist of an array of values or a range of cells, each of which can contain many values.

Excel Sum Function Examples

The following spreadsheet shows three simple examples of the Excel Sum function:

Formulas:
AB
15=SUM( 15, 29 )
27=SUM( A1, A2 )
39=SUM( A1:A3 )
Results:
AB
1544
2712
3921

The above examples show that each argument to the Sum function can be supplied as a single value or cell reference, or as an array of values or cells.

For further examples of the Excel Sum function, see the Microsoft Office website.

Sum Function Error

If you get an error from the Excel Sum Function, this is likely to be the #VALUE! error:

Common Error
 #VALUE! - Occurs if any of the number arguments that are supplied directly to the Sum function can not be interpreted as numeric values.(Note that if a supplied range of cells contains values that cannot be interpreted as numbers, these are ignored by the function - see the table below).

Which Values are Included in the Calculation?

Numbers and dates are always counted as numeric values by the Excel Sum function. However, text representations and logical values are handled differently, depending on whether they are values stored in the cells of your spreadsheet, or they are supplied directly to the function.

The table below summarises which values are included in the Excel Sum Function calculation, and which values are ignored or result in errors:

Value Within a
Range of Cells
Value Supplied
Directly to Function
NumbersIncludedIncluded
DatesIncludedIncluded
Logical ValuesIgnoredIncluded
(True=1; False=0)
Text Representations of
Numbers & Dates
IgnoredIncluded
Other TextIgnored#VALUE! Error
ErrorsErrorError