SUM Function – Google Sheets

The SUM function is the most popular function in Google Sheets. The syntax is easy to remember and works similarly across most spreadsheet programs.

Feel free to copy the template with these examples to follow along.

Purpose

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

Video Explanation

YouTube player

Syntax

=SUM(value1, [value2,...])

  • value1 – The first number or range to add together.
  • value2, … – [OPTIONAL] Additional numbers or ranges to add to value1.

ADD – Calculates the sum of two numbers. Equivalent to the + operator

SUMIF – Add numbers if it meets specific criteria

Examples

Example 1 – Sum Values in a Continuous Range

simple example of the SUM function
This SUM function uses a continuous range.

You can calculate the total sales using the formula =SUM(B2:B9) shown in the image above. The range of cells, in this case, is continuous, so you only need to specify one 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, we must change the formula. We want to exclude Paul and Jessica from the summation in the dataset below. Since the values are no longer in one range, the formula changes to =SUM(B2:B4, B7:B9). As a result, the total excludes Paul and Jessica’s sales this time.

adding data in non-continuous cells with the SUM function
This SUM function is summing two ranges.

Example 3 – Adding a Row

The cell range that you use does not have to be a column.

The SUM function adding a row
Adding a Row

As seen in the image above, you can designate a horizontal area as your range, similar to a vertical range, but the column letter change instead of the row numbers. The range B2:D2 tells Google Sheets to start in cell B2 and go to the right until D2.

Example 4 – Adding Columns and Rows

Cell ranges can also be multiple columns and rows.

Adding columns and rows
Adding Columns and Rows

In this example, the cell in the upper left is B2, and the lower right is D4. You refer to this as B2:D4.

Live Template with Examples

See this template for a spreadsheet with the examples used in this blog. You can make a copy and use them in your work.

Additional Notes

The SUM function has some shortcomings, such as accidentally double counting when multiple SUMs are in a column. The function adds every cell given as input, even if it is already a subtotal. In such instances, the SUBTOTAL function might be more helpful as it can ignore other subtotals. Also, the SUBTOTAL function can ignore an error as input, but the SUM function will not return a value if any of the inputs are an error.