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: Values in all arguments are summed together, so the following formulas are equivalent: In the example shown, the formula in D12 is: References do not need to be next to one another. For example:
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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
