Table of Contents
The SUBTOTAL function in Google Sheets allows users to perform calculations on a selected range of data while excluding any hidden or filtered cells. When used in combination with the COUNTIF function, it can provide a more accurate count of cells that meet a specific criteria within a range. This can be particularly useful in data analysis and reporting, where only visible data is needed for accurate calculations. By using the SUBTOTAL function with COUNTIF in Google Sheets, users can streamline their data analysis process and obtain more precise results.
Google Sheets: Use SUBTOTAL with COUNTIF
Often you may want to use the SUBTOTAL function with the COUNTIF function in Google Sheets to count 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 COUNTIF
Now suppose that we would like to count the number of values in the Position column that are equal to Guard.
=COUNTIFS(B2:B9,"Guard",D2:D9,"1")
The following screenshot shows how to use this formula in practice:
The formula correctly returns a count of 2.
By looking at the filtered data, we can verify that there are indeed 2 rows in the Position column with a value of Guard.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets: