How to Count Filtered Rows in Excel?

If you want to count filtered rows in Excel, you can do that using the SUBTOTAL function.

The SUBTOTAL function allows you to perform a regular count on a column with the ability to exclude those rows that have been filtered out.

In this short tutorial, I will show you how to use the SUBTOTAL function to count filter rows in Excel, and another visual way to quickly identify the total number of filtered rows.

Count Filtered Rows Using SUBTOTAL Function

Below is a data set where I have the employee’s name in column A, the department name in column B, and the reporting manager’s name in column C.

I have now applied a filter to this data set and filtered out the rows where the department name is finance. So you get the dataset as shown below.

Now I want to get the count of filtered rows only.

Here is the SUBTOTAL formula that will give me the count of only those rows that are visible after the filtering is applied:

=SUBTOTAL(3,B2:B10)

The above formula gives us 6, which is the total number of rows that we have left after we have filtered the data set.

In the above subtotal function, we have used two arguments:

• 3 as the first argument – the first argument of the SUBTOTAL function always needs to be a numeric value that tells the function what kind of operation it needs to do on the following range. Using 3 here tells the function that it needs to give the count of the range (specified in the second argument) and only count the visible rows.
• B2:B10 – this is the range on which the count is done.

Note: When you use 3 as the first argument, it is going to exclude the rows that have been filtered out (counts only the visible rows). In case you have not filtered your data set and have hidden some rows manually, these would still be counted. However, if you have hidden some rows and then filtered the data, then the hidden rows will not be counted (see the table below for more clarity).

Apart from the above formula, you can also use the below SUBTOTAL formula:

=SUBTOTAL(103,B2:B10)

The above formula also works the same way, but when you 103, it will also ignore the hidden rows (i.e., not count the hidden rows) in an unfiltered dataset.

Note that these two arguments in the formula (3 and 103) work exactly the same when you use them with filters dataset. Both of these functions will not count rows that have been filtered out as well as those that are hidden.

But if you’re still confused about these two formulas, the table below should clear any confusion.

In short, if you have a filtered dataset, both 3 and 103 will give you the same result.

The above formulas would work with Excel Tables as well.

Also read: Select Visible Cells in Excel

Check the Count of Filtered Rows in the Status Bar

Not many Excel users know this, but the count of filtered rows is also shown in the status bar.

As soon as you apply a filter to your data set, you will notice that the status bar shows you how many cells are visible after the filter from the total number of cells.

Something like 6 of 9 records found – where 6 is the number of records visible after the filter has been applied and 9 is the total number of records in the data set.

Note: Hidden rows do not impact this. It only tells you how many records should be visible after the filtering. If you manually hide any of the rows, that would still be counted.

In this tutorial, I showed you how to use the subtotal function to count filtered rows in Excel. I covered the difference between the two arguments you can use in these SUBTOTAL functions.

And I have also covered how to get the count of filtered rows through the status bar.

Other Excel articles you may also like:

• Delete All Hidden Rows and Columns in Excel
• Filter By Color in Excel
• Paste into Filtered Column (Skipping Hidden Cells)
• Excel Filter Function
• Filter Cells that have Duplicate Text Strings (Words) in it
• Dynamic Excel Filter Search Box – Extract Data as you Type
• How to Count Colored Cells in Excel