SUM Function

The SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.

The SUM function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range. All numbers in the arguments provided are summed. The SUM function automatically ignores empty cells and text values, which makes SUM useful for summing cells that may contain text values.

The SUM function will sum hardcoded values and numbers that result from formulas. If you need to sum a range and ignore existing subtotals, see the SUBTOTAL function.

Examples

Typically, the SUM function is used with ranges. For example:

=SUM(A1:A9) // sum 9 cells in A1:A9
=SUM(A1:F1) // sum 6 cells in A1:F1
=SUM(A1:A100) // sum 100 cells in A1:A100

Values in all arguments are summed together, so the following formulas are equivalent:

=SUM(A1:A5)
=SUM(A1,A2,A3,A4,A5)
=SUM(A1:A3,A4:A5)

In the example shown, the formula in D12 is:

=SUM(D6:D10) // returns 9.05

References do not need to be next to one another. For example:

=SUM(A1,F13,E100)

Sum with text values

The SUM function automatically ignores text values without returning an error. This can be useful in situations like this, where the first formula would otherwise throw an error.

Keyboard shortcut

Excel provides a keyboard shortcut to automatically sum a range of cells above. You can see a demonstration in this video.

Notes

• SUM automatically ignores empty cells and cells with text values.
• If arguments contain errors, SUM will return an error.
• The AGGREGATE function can sum while ignoring errors.
• SUM can handle up to 255 total arguments.
• Arguments can be supplied as constants, ranges, named ranges, or cell references.