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.
This function returns the total of a series of numbers, cells, or both.
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
SUMIF – Add numbers if it meets specific criteria
Example 1 – Sum Values in 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.
Example 3 – Adding a Row
The cell range that you use does not have to be a column.
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
Example 4 – Adding Columns and Rows
Cell ranges can also be multiple 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
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.
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.