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.

Data set without the filter

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.

Data set when the filter has been applied

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)
Using subtotal function to count filtered rows in Excel

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.

Dataset ConfigurationUsing 3Using 103
Rows not hidden and not filteredCounts all visible rowsCounts all visible rows
Rows hidden and not filteredHidden rows are countedHidden rows are not counted
Rows not hidden and filteredOnly visible rows are counted. Filtered-out rows are not countedOnly visible rows are counted. Filtered-out rows and hidden rows are not counted.
Rows are hidden and filteredOnly visible rows are counted. Filtered-out rows and hidden rows are not counted.Only visible rows are counted. Filtered-out rows and hidden rows are not counted

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.

Count of filtered rows in the status bar in Excel

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

1 thought on “How to Count Filtered Rows in Excel?”

  1. Hi
    Can you tell me the formula for identifying a specific set of text within a set of data and its number of occurances excluding flitered data.
    Thanks

    Reply

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster