The Excel SUM Function


Basic 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. These can be supplied to the function either directly, as values returned from other functions, or as references to cells containing numeric values.

In Excel 2007 or 2010, 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.


Which Values are Included in the Excel Sum Function 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
Numbers Included Included
Dates Included Included
Logical Values Ignored Included
(True=1; False=0)
Text Representations of
Numbers & Dates
Ignored Included
Other Text Ignored #VALUE! Error
Errors Error Error


Excel Sum Function Examples

The following spreadsheet shows the Excel Sum function used to calculate the sum of the numbers 5, 6, 7, 8 & 9. In each of the five examples, the numbers are supplied to the Sum function differently.

The spreadsheet on the left shows the formulas and the spreadsheet on the right shows the results.

 Formulas:
  A B
1 5 =SUM( A1:A5 )
2 6 =SUM( 5+6, 7, 8, 9 )
3 7 =SUM( A1:A3, 8, 9 )
4 8 =SUM( A1, A2, A3, "8", "9" )
5 9 =SUM( {5,6,7}, 8, 9 )
 Results:
  A B
1 5 35
2 6 35
3 7 35
4 8 35
5 9 35

Note that, in the above example spreadsheet:


Further examples of the Excel Sum function can be found on the Microsoft Office website.


Sum Function Errors

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 supplied arguments can not be interpreted as numeric values
Return to the Excel Math Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net