SUM Function

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.

Purpose

The SUM function returns the total of a series of numbers, cells, or both.

Video Explanation

Thumbnail for video tutorial on the SUM function
SUM Function Video Tutorial

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

SUM function in a continuous range.
The SUM function using 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.

Sum of B2:B4 and B7:B9

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.

Leave a comment

© 2020 – 2022 learnspreadsheet.com