Use the SUBTOTAL function to count rows in filtered lists - Basic Statistical Functions - Microsoft Excel Tutorials

Use the SUBTOTAL function to count rows in filtered lists using Microsoft Excel

When using the Filter option in the Data menu, it is recommended that the COUNT and COUNTA functions not be used because in a filtered worksheet they count both visible and hidden rows. Instead, use the SUBTOTAL function to get the subtotal of a list or database that is visible. The syntax for the SUBTOTAL function was presented earlier.

To count rows in a filtered list:

1. In cells B2:B10 type group numbers from 1 to 3.
2. In cells C2:C10 type the daily sales of each group.
3. From the Data menu, select Filter | AutoFilter.
4. Select group 1 in the column B filter.
5. Select cell C12 and type the following formula: =SUBTOTAL(2,C2:C10) & " rows in filter".

Use the SUBTOTAL function to count rows in filtered lists

Note: Use the function =SUBTOTAL(3,B2:B10) & " rows in filter" in cell B12 to count the filtered cells as seen in Figure