Table of Contents
“SUBTOTAL with SUMIF in Google Sheets” is a powerful combination that allows users to calculate subtotal values based on specific criteria within a range of data in Google Sheets. By using the SUMIF function, users can first filter their data based on a particular condition, and then use the SUBTOTAL function to calculate the sum of the filtered values. This provides a more efficient and accurate way of obtaining subtotal values, especially when working with large datasets. Additionally, this feature also allows for dynamic updating of the subtotal values as the data in the range changes. Overall, the use of SUBTOTAL with SUMIF in Google Sheets is a valuable tool for data analysis and organization.
Google Sheets: Use SUBTOTAL with SUMIF
Often you may want to use the SUBTOTAL function with the SUMIF function in Google Sheets to sum only the visible rows that meet some criteria.
Fortunately this is easy to do by using a helper column.
The following step-by-step example shows how to do so in practice.
Step 1: Enter the Data
First, let’s enter the following dataset that contains information about various basketball players:
Step 2: Add Helper Column
Next, type the following formula into cell D2:
=SUBTOTAL(103,C2)
Then click and drag this formula down to each remaining cell in column D:
Step 3: Filter the Data
To add a filter to this data, we can highlight cells A1:D11, then click the Data tab, then click Create a filter.
Then click the filter icon next to the Conference column and filter the data to only show rows where the value in the Conference column is equal to West:
Step 4: Use Subtotal with SUMIF
Now suppose that we would like to sum the values in the Points column only for players who have a Position value of Guard.
=SUMIFS(C2:C11,B2:B11,"Guard",D2:D11,"1")
The following screenshot shows how to use this formula in practice:
The formula correctly returns a sum of 31.
By looking at the filtered data, we can verify that the sum of the values in the Points column among players with a Position of Guard is indeed 31:
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets: