How to Count Filtered Rows in Excel

To count filtered rows in Excel, you can use the SUBTOTAL function, which works with filtered data and ignores hidden rows. The SUBTOTAL function can perform various operations like counting, sum, average, etc., on visible cells only.

Here are the steps to count filtered rows in Excel:

1. Apply a filter to your data: Click any cell within your data range, go to the "Data" tab in the Excel ribbon, and click "Filter." This will add filter arrows to the headers of your data.
2. Filter your data: Click the filter arrow in the header of the column you want to filter by and select the criteria you want to filter.
3. In an empty cell, enter the SUBTOTAL function: Type the following formula (replace "A2:A100" with the range of cells you want to count the visible rows for):

=SUBTOTAL(2, A2:A100)

The first argument (2) in the SUBTOTAL function indicates that you want to perform the "count" operation. The second argument (A2:A100) is the range of cells you want to count the filtered rows for.

4. Press Enter to get the count of filtered rows in the specified range.

Now, whenever you change the filter criteria, the count of filtered rows will update automatically.

Example

Let's say you have the following dataset in Excel:

A         B
----------------
Name      Age
Alice     25
Bob       30
Charlie   22
David     30
Eva       25

You want to count how many people are 25 years old after applying a filter.

1. Click any cell within your data range (e.g., A1).
2. Go to the "Data" tab and click "Filter."
3. Click the filter arrow in the "Age" column header (cell B1) and select "25" as the filter criteria.
4. In an empty cell, enter the SUBTOTAL function:

=SUBTOTAL(2, B2:B6)

5. Press Enter.

The result will be 2, as there are two people with the age of 25 in the filtered data (Alice and Eva).