How to Count Filtered Rows in Google Sheets

To count filtered rows in Google Sheets, you can use the SUBTOTAL function. The SUBTOTAL function allows you to perform various calculations on visible rows only, ignoring the hidden rows due to filtering.

Here's a step-by-step guide on how to count filtered rows in Google Sheets:

1. Apply a filter to your data range by selecting the range and clicking on "Data" > "Create a filter" in the menu.
2. Use the filter options to show only the rows you want to count.
3. In an empty cell, enter the SUBTOTAL function with the following syntax:

=SUBTOTAL(function_code, range)

• function_code: Use 3 for COUNTA (which counts non-empty cells) or 2 for COUNT (which counts numeric cells only).
• range: The range of cells you want to count the filtered rows in.

For example, if you want to count the non-empty filtered rows in column A from A2 to A100, you can use the following formula:

=SUBTOTAL(3, A2:A100)

Or, if you want to count the numeric filtered rows in column A from A2 to A100, you can use:

=SUBTOTAL(2, A2:A100)

The result will show the total count of visible rows in the specified range based on the applied filters.

Example

Let's say you have a list of sales data in columns A to C, and you want to count the number of visible rows based on the applied filters in the range A2:A100.

1. Select the range A1:C100 and click on "Data" > "Create a filter."
2. Use the filter options to show only the rows you want to count.
3. In an empty cell, enter the following formula to count filtered rows:

=SUBTOTAL(3, A2:A100)

4. Press Enter, and the result will display the total count of visible rows based on the applied filters in the range A2:A100.