Summary
The Excel SUM function calculates the total sum of a range of numbers. This function is one of the most basic and frequently used functions in Excel, ideal for adding up rows or columns of numbers.
Syntax
=SUM(number1, [number2], ...)
- number1: The first number or range to add.
- [number2], …: [Optional] Additional numbers or ranges to add.
Return value
The sum of the given numbers.
How to use
Use the SUM function to add up a series of numbers, cell references, ranges, or a combination of these. It’s particularly useful in financial analysis, budgeting, accounting, and data analysis scenarios.
Examples
Simple SUM
Adding Numbers in a Range: To sum numbers in the range A2:A10:
=SUM(A2:A10)
This formula sums up the values in A2:A10.
SUM with Individual Cells
Summing Individual Cells: Adding up values from separate cells:
=SUM(B2, B4, B6)
Sums up the values in cells B2, B4, and B6.
SUM with Mixed References
Combining Ranges and Single Cells: To add a range and individual cells:
=SUM(C2:C10, D2, D4)
This sums the range C2:C10 and the individual cells D2 and D4.
SUM of Filtered Ranges
Summing Based on Multiple Conditions in a Table: Calculating the sum of a column in a table, considering multiple conditions:
=SUM(Table1[Sales] * (Table1[ProductType] = "Electronics") * (Table1[Region] = "Europe"))
This formula calculates the total sales from ‘Table1’ where ‘ProductType’ is ‘Electronics’ and ‘Region’ is ‘Europe’. It multiplies the ‘Sales’ column with boolean expressions that evaluate to 1 (TRUE) or 0 (FALSE) for each row, effectively summing only those rows that meet both conditions.
SUM for Cumulative Total
Calculating a Running Total in a Column: Generating a running sum in a data column:
=SUM($A$2:A2)
This formula, when dragged down from the initial cell, provides a running total of the values in column A. It keeps the starting cell of the range fixed ($A$2) and extends the ending cell relatively (A2, A3, A4, etc.) as it’s copied down the column.
Additional Notes
- The SUM function can handle up to 255 individual arguments.
- It automatically ignores text and empty cells within a range.