Sum (add) values together.
=SUM(number1,number2,…)
SUM defines the 'numbers to sum', up to 255 values including individual values (1,2,3), cell references (A1,A2,A3), ranges (A1:A3) or a mix of all three. Non-numeric values (e.g. text) are ignored by the SUM function. Dates and times can be nested within the SUM function.
It is recommended to use SUM with cell references and ranges (e.g. =SUM(A1:A3)) where possible rather than adding individual values (e.g. =A1+A2+A3).
- Cell references and ranges will automatically update if rows or columns are inserted into / deleted from the SUM area whereas =A1+A2+A3 could throw a #REF! error.
- Text values and empty cells are ignored by the SUM function whereas =A1+A2+A3 could throw a #VALUE! error if one of the cells contained a text string.
SUM and numbers.
In the examples below, the SUM function is used to add population numbers for contiguous and non-contiguous ranges.
Use the SUM function to add values together.
SUM and dates / times.
In the examples below, the SUM function is used to calculate the number of days between dates or the number of hours between different times.
Use the SUM function to work with dates and times.
Syntax
=SUM(number1,number2,…)
The SUM function consists of up to 255 arguments:
- number1. Required. The first value to sum.
- number2. Optional. The second value to sum.
- .... Optional. Additional values (up to 255) to sum.
Try it now!
- Enter your formulas into the grey cells.
Scope
SUM() can be applied to cell references, ranges, named ranges, tables, constants and arrays.
|
=SUM(C3,C5,C7) |
|
=SUM(C3:C7) |
|
=SUM(myNamedRange) |
|
=SUM(Table1[ColumnName]) |
|
=SUM(number1,number2,number3,...) OR =SUM(number1+number2+number3+...) |
|
=SUM(LEN(B3:B5)) |
Caveats
The SUM() function:
- can handle up to 255 individual arguments.
- will ignore text values and return the sum of just the numeric values. This prevents a #VALUE! error.
- will automatically update if rows or columns within the range referenced in the formula are deleted. This prevents a #REF! error.
- will update to include added rows or columns (assuming they are within the range referenced in the formula).
- has a keyboard shortcut to automatically sum the range of cells above.
- will return an error if the arguments contain errors.
Keyboard Shortcut
- Use the SUM() function keyboard shortcut (Σ) to AutoSum a range of cells.
- To sum a column of numbers, select the cell immediately below the last number in the column.
- To sum a row of numbers, select the cell immediately to the right.
- AutoSum can be found both on the Home (Editing group) and Formulas (Function library group) tabs.
- AutoSum can be copied to different cells and the range of cells referenced will adjust automatically.
Common issues
- SUM will return an error if one of the arguments contains an error.
Related Functions
- SUMIF() and SUMIFS() sum (add) values in a range that meet specific or multiple criteria.
- COUNT(), COUNTA(), COUNTBLANK(), COUNTIF() and COUNTIFS() count values within a range.
Functions Category