SUM function

The SUM function in Excel adds together a range of numbers, allowing you to quickly calculate the total of a group of values. It is one of the most commonly used functions in Excel for basic arithmetic.

Syntax

=SUM(number1, [number2], ...)

Parameters

• number1 (required): The first number or range you want to sum. This can be a direct number, a reference to a cell, or a range of cells.
• [number2, …] (optional): Additional numbers, cell references, or ranges to add together.

Key Points

1. Multiple Numbers or Ranges: You can add as many numbers or ranges as needed by separating them with commas.
2. Range References: You can sum a continuous range of cells using a colon (e.g., A1:A10).
3. Non-contiguous Ranges: You can sum multiple, non-adjacent ranges by specifying them as separate arguments (e.g., A1:A10, C1:C10).
4. Cell References: You can reference individual cells or ranges in your formula (e.g., =SUM(A1, B1, C1)).

Examples

1. Sum of numbers in a range (A1:A5):

   =SUM(A1:A5)
   

   Result: The sum of the values in cells A1, A2, A3, A4, and A5.

2. Sum of multiple non-adjacent ranges (A1:A5, C1:C5):

   =SUM(A1:A5, C1:C5)
   

   Result: The sum of the values in cells A1 to A5, and C1 to C5.

3. Sum of individual numbers:

   =SUM(10, 20, 30)
   

   Result: 60 (since $10 + 20 + 30 = 60$)

4. Sum of cells in a row (A1 to E1):

   =SUM(A1:E1)
   

   Result: The sum of values in cells A1, B1, C1, D1, and E1.

5. Sum of a dynamic range (A1:A10, where values might change):

   =SUM(A1:A10)
   

   Result: This formula will continuously update as you add or remove values in the range A1:A10.

Notes

• Text and Non-Numeric Data: The SUM function ignores text, logical values (TRUE/FALSE), and empty cells in the specified ranges.
• Cells Containing Numbers Stored as Text: If cells contain numbers formatted as text, SUM will ignore them. You can use the VALUE function to convert text-based numbers to actual numeric values if needed.
• Error Handling: If a range includes errors (e.g., #DIV/0!), the SUM function will return an error unless combined with error handling functions like IFERROR.

Related Functions

• SUMIF: Adds numbers that meet a specific condition.
• SUMIFS: Adds numbers that meet multiple conditions.
• AVERAGE: Calculates the average of a range of numbers.
• COUNT: Counts the number of cells containing numbers in a range.
• SUMPRODUCT: Multiplies corresponding elements in arrays and returns the sum of those products.

The SUM function is essential for financial, statistical, and general-purpose calculations, making it one of Excel’s most used and versatile functions.