How to Count Filtered Rows in Excel

To count filtered rows in Excel, you can use the SUBTOTAL function that allows you to select its count feature in the first argument and the range from which you want to count filtered rows. After using this formula, when you apply a filter on that range and filter cells, it will only count the visible rows.

=SUBTOTAL(3,A2:A101)
=SUBTOTAL(103,A2:A101)

In the above example, we have 100 cells with values in column A. Now you need to count the rows once you apply filter cells.

count-filtered-rows

Steps to Count Filtered Rows

  1. First, in cell B2, enter the function SUBTOTAL.
  2. Now, in the first argument, select function_num COUNTA or enter 3.
  3. After that, in the second argument, refer to the range A1:A101.
  4. Next, enter the closing parentheses to close the function and enter.
  5. In the end, filter column A to see that your formula shows you the count of filtered rows in the result.
use-subtotal-function
=SUBTOTAL(3,A2:A101)

Using this SUBTOTAL function helps you in other ways, too. Let’s say you have a few rows hidden, in that case also, it will give you a count without those hidden rows.

count-without-hidden-rows

In the above example, we have four rows hidden, and it has returned 96 in the result.

=SUBTOTAL(103,A2:A101)

Remember, there are two different ways to count rows once you apply the filter.

  • Count with Hidden Rows
  • Count without Hidden Rows
subtotal-with-hidden-and-non-hidden-rows

Download Sample File