How to Count Filtered Rows in Excel?

by

We sometimes need to know how many rows have been filtered after applying the filters.

Counting the number of filtered rows is slightly different, as we only want to count the rows that are visible and not the ones that have been filtered and hidden.

In this article, I will show you some simple methods you can use to count filtered rows in Excel.

Whether you’re a seasoned Excel pro or just starting out, this nifty trick will save you time and headaches. So let’s get counting!

Method 1 – Get the Filtered Row Count in Status Bar

Below I have a dataset of some employees who joined a supermarket. 

The month is listed in column A, the employee’s name is listed in column B, and the employee’s age is listed in column C.

Let’s say that you have filtered the above table to February and April months as below.

Now we want to know how many rows are filtered.

You can easily do it by selecting the data area of one of the columns in the above table, like the one below.

Then you can see the number of filtered rows in the status bar of your Excel worksheet.

In this case, it is showing the number of filtered rows as 4.

Note: Sometimes, you may not see the count in your Excel status bar. In such cases, right-click on your status bar and make sure you have checked the COUNT option, as in the below image.

Also read: Count Cells that are Not Blank in Excel (Formulas)

Method 2 – Using the SUBTOTAL Function to Count Filtered Rows in Excel

Another quick way to get the count of the filtered row is by using the SUBTOTAL function.

The SUBTOTAL function has many different modes, where one allows us to get the count of only those cells that are visible after the filtering.

A dataset of some new employees at a supermarket is provided below.

The month of joining, the employee’s name, and their age are recorded in columns A, B, and C, respectively.

Let’s say that you have filtered the above table to show only the records for February and April as shown below.

Now we want to know how many rows are filtered.

We can use the SUBTOTAL function to find the number of rows filtered in Excel.

=SUBTOTAL(2,C2:C9)

The syntax of the SUBTOTAL function is SUBTOTAL(function_num,ref1,[ref2],…). 

The first argument is the function number, and we can use function number 2 to get the COUNT function.

The COUNT function calculates how many numbers are entered in the selected range.

For the next argument, you have to select a column that contains numbers. As the age column contains numbers, we can select the cell range C2:C9 as the second argument.

If your table does not have any number column, you have to modify the SUBTOTAL function as given below.

=SUBTOTAL(3,C2:C9)

In the above formula, we have changed the function number to 3 in order to get the COUNTA function.

The COUNTA function calculates the number of cells with any entry regardless of the type of information.

So, we can count numbers, text entries, errors, etc. with the COUNTA function. Therefore, you can select any column of your data table as the second argument.

The above SUBTOTAL formula is dynamic and would automatically update if the filter is changed (so it always shows the count of visible cells only). In other words, once you create the formula, you don’t need any adjustment to the formula to get the result when the filter is changed.

Also read: Count Cells Less than a Value in Excel (COUNTIF Less)

Method 3 – Using the AGGREGATE function to count filtered rows in Excel

Even though the AGGREGATE function is more similar to the SUBTOTAL function, the AGGREGATE function is smarter than the SUBTOTAL function.

The reason is it has more options, such as counting visible cells while ignoring error values and nested SUBTOTAL and AGGREGATE functions etc.

This is a dataset of some recent supermarket hires. Columns A, B, and C list the month of joining, the employee’s name, and their age.

Let’s say that you have filtered the above table to February and April months as below.

Now we want to know how many rows are filtered.

We can use the AGGREGATE function to find the number of rows filtered in Excel.

=AGGREGATE(2,5,C2:C9)

The syntax of the AGGREGATE function is AGGREGATE(function_num, options, ref1, [ref2], …).

The aggregate function helps to find a specified calculation of a selected range by ignoring cells as per the selected option. 

The first argument of this function is the function number. Use number 2 as the function number to get the COUNT function.

The second argument of the function is the options. In our example, we need to ignore all the values in the hidden rows. So, we can select the number 5 from the options.

Next, we have to select a data range of a column that contains numbers. We can select the age column as the values in that column are numbers.

If your table does not have any number column, you have to modify the AGGREGATE function as given below.

=AGGREGATE(3,5,C2:C9)

In the above formula, we have changed the function number to 3 in order to get the COUNTA function.

The COUNTA function calculates the number of cells with any entry regardless of the type of information. Therefore, you can select any column of your data table as the second argument.

Note – If you want to ignore both hidden rows and error values for your count, use 7 as the option number instead of 5.

Also read: How to Add a Total Row in Excel Table

Method 4 – Using the COUNTIF to Count Filtered Rows in Excel

Sometimes we need to count rows from the filtered rows that fulfill particular criteria.

While the above methods only allow us to count the total number of filtered rows, we can combine the COUNTIF function with SUBTOTAL to count filtered rows with a condition (such as counting all filtered rows where the age is more than 30).

A dataset of some new employees at a supermarket is given below. The month of joining is in Column A, the employee’s name is in Column B, and the employee’s age is in Column C.

Let’s say that you have filtered the above table to February and April months as below.

Now we want to know how many rows are filtered.

You can create a helper column and use the COUNTIF function to get the number of rows filtered.

As the first step, we must create a helper column, as shown in the image below.

The below formula is used in the helper column.

=SUBTOTAL(3,C2)

The syntax of the SUBTOTAL function is  SUBTOTAL(function_num,ref1,[ref2],…).

The first argument is the function number, and we can use function number 3 to get the COUNTA function.

The COUNTA function calculates how many entries are entered in the selected range. For the next argument, you have to select a cell from the table which is in the same row. In this case, we have selected cell C2.

When a row is hidden, the result of the formula is 0, and otherwise, the result is 1.

Now we can apply the below COUNTIF formula to calculate how many rows are filtered.

=COUNTIF(D2:D9,1)

The syntax of the COUNTIF function is COUNTIF(range, criteria). The first argument of the function is the range.

We have to select the range that contains the criteria. So, in this case, we have to select all the values in column D.

Next, we have to enter the criteria. We have to count the visible cells. The value of visible cells in column D is 1. Therefore, our criteria for the COUNTIF function are 1.

In the above example. I have used the COUNTIF function to count all the visible filtered cells. In case you want to count the rows that are visible and where the age is more than 30, you can the below formula:

=COUNTIFS(D2:D9,1,C2:C9,">30")

To count filtered rows in Excel, you can use any of the methods listed above. But, before selecting any of the columns, you must first check whether that column has any blank cells.

If so, you must use a different column because, in all of the above methods, Excel counts the number of cells that have a value.

So these are some of the ways you can use to count filtered rows in Excel. The easiest way is to select the filtered cells and look at the status bar.

You can also use the SUBTOTAL function or the AGGREGATE function to do this. And if you want to count filtered rows along with some other condition, you can use the COUNTIFS function.

I hope you found this Excel tutorial useful. Do let us know your thoughts in the comments section.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment