Table of Contents
Google Sheets offers a powerful way to use the SUM function to sum filtered rows. This allows you to quickly calculate the sum of a range of values based on criteria you specify. To do this, you can use the SUMIF, SUMIFS, and DSUM functions. Each of these functions takes different parameters, so you should read the help documentation for the one you intend to use before implementing it. Examples of how to use these functions can be found in the Google Sheets help documentation, as well as online.
The easiest way to take the sum of a filtered range in Google Sheets is to use the following syntax:
SUBTOTAL(109, A1:A10)
Note that the value 109 is a for taking the sum of a filtered range of rows.
The following example shows how to use this function in practice.
Example: Sum Filtered Rows in Google Sheets
Suppose we have the following spreadsheet that contains information about various basketball teams:
To add a filter to this data, we can highlight cells A1:B10, then click the Data tab, then click Create a filter:
We can then click the Filter icon at the top of the Points column and uncheck the box next to the first three values 84, 89, and 93:
Once we click OK, the data will be filtered to remove these values.
If we attempt to use the SUM() function to sum the points column of the filtered rows, it will actually return the sum of all of the original values:
Instead, we can use the SUBTOTAL() function:
This function takes the sum of only the visible rows.
Sum of Visible Rows: 99 + 94 + 97 + 104 + 109 + 99 = 602.
The following tutorials explain how to perform other common operations in Google Sheets:
Google Sheets: How to Sum If Checkbox is Checked