The SUM function is one of the most utilized functions in spreadsheets. The syntax is easy to remember and works the same across most spreadsheet programs. In this article, we are going to explore its usage in spreadsheet.com.
Feel free to make a copy of the 🔗template with these examples to follow along. 🔒Templates with further examples covered in the video are available to members of learnspreadsheet.com.
Contents
Purpose
The SUM function returns the total of a series of numbers, cells, or both.
Video Explanation
Syntax
=SUM(value1, [value2,...])
value1– The first number or range to add together.value2, …– [OPTIONAL] Additional numbers or ranges to add to value1.
Examples
Example 1 – Sum Values in a Continuous Range
In the above image, you can get the total sales to figure using the formula =SUM(B2:B9). The dataset, in this case, is continuous, so you only need to specify the range to add together inside the function. What if we were working with scattered values that are not necessarily in adjacent rows or columns?
Example 2 – Sum Values Across Non-Adjacent Cells
If the values we wish to add are not adjacent to each other, we have to tweak the formula a little bit. In the above dataset, we’d like to exclude Paul and Jessica from the summation. The formula would change to =SUM(B2:B4, B7:B9). As a result, the total this time excludes Paul and Jessica’s sales.
Note: The SUM function has some shortcomings, such as accidentally double counting when multiple SUMs are in a column. Every cell given as input to the function is added, even if it is already a subtotal. In such instances, the SUBTOTAL function might be more helpful as it can ignore other subtotals.