Introduction

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.

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.

=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.

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

Conclusion :

Counting filtered rows in Excel is a useful skill that can save you time and effort when working with large sets of data. Whether you prefer to use the Status Bar, the SUBTOTAL function, or the AGGREGATE function, there are multiple ways to count filtered rows in Excel. By mastering these techniques, you can become a more efficient and effective Excel user.