How to Count Filtered Rows in Excel (Visible Rows)

Steps to Count Filtered Rows in Excel

To write this formula, you can download the sample file and then follow the below simple steps:

=SUBTOTAL(3,A2:A101)

1. Starting from cell B2, enter (=) in the cell and then enter the SUBTOTAL function to begin entering the function.
2. Next, in the function’s first argument, function_num, select 3 from the drop-down list, or you can also type 3 in the argument.
3. After that, in the second argument, refer to the range A1:A101. This needs to be the entire range of data you have.
4. Now, enter the closing parentheses to close the function and hit enter to get the result in the cell. When you hit enter, it gives you the count of the total rows in the data.
5. Finally, filter column A to see that your formula shows the count of filtered rows in the result.

Using this SUBTOTAL function helps you in other ways, too.

Read Also – How to Count Colored Cells in Excel

3 Vs. 103 in SUBTOTAL

When you use function number 3 for counting, the SUBTOTAL function will include visible and hidden rows in its calculation.

If you have some rows hidden in your worksheet, SUBTOTAL will still count those rows.

On the other hand, when you use function number 103 for counting, the SUBTOTAL function will ignore the hidden rows in the calculation and only count or perform the calculation on the visible rows.

It can be beneficial if you have applied filters to your data and want to perform calculations only on the visible, filtered rows.

For example, if you have a few rows hidden instead of the filter, SUBTOTAL will still count those rows. See the example below. We have used the simple formula with function number 3.

However, when we use the 103 instead of the 3 in the function number, it shows the count of the unhidden rows.

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

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

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

• Count with Hidden Rows: =SUBTOTAL(3,A2:A101)
• Count without Hidden Rows: =SUBTOTAL(103,A2:A101)

Using AGGREGATE to Count Filtered Rows

The same thing can be done with the AGGREGATE function. It is such a powerful function that it has extended powers to SUBTOTAL. The syntax for this function is =AGGREGATE(function_num, options, ref1, [ref2], …).

In our example, the function_num would be “5” to tell the function to ignore hidden rows, and ref1 would be the range of cells you want to count.

So, if you wanted to count the rows in column A from A2 to A100, your function might look like this: =AGGREGATE(3, 5, A2:A100).

Count Filtered Rows with a Condition (SUMPRODUCT)

If you need to count filtered rows in Excel with a specific condition using the SUMPRODUCT function.

You’ll need to type in the following formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A14,ROW(A1:A14)-MIN(ROW(A1:A14)),,1)), --(A1:A14=1))

Here is a breakdown of this formula:

SUBTOTAL(3,OFFSET(A1:A14,ROW(A1:A14)-MIN(ROW(A1:A14)),,1))

It is used to construct an array of 1’s and 0’s corresponding to visible and hidden rows. If a row is visible, it will return 1; if it is hidden, it will return 0.

--(B2:B7>0)

It is a condition that checks if the value in column A equals 1. The — operator changes TRUE to 1 and FALSE to 0.

SUMPRODUCT

It multiplies these two arrays together and sums the result. The sum is the count of visible rows where the value in column B is greater than 0.

This formula is particularly useful because it counts only the visible rows in column B where the value is greater than 0, ignoring any hidden or filtered rows.

It’s a robust Excel data analysis tool, especially when working with large data sets where manual counting would be impractical or time-consuming.

Create a Custom Function to Count the Filtered Rows

If you want to use VBA, write a code like the following to count the filtered rows with a single function. You can add the code below to the Visual Basic editor and then use the function in a cell in the worksheet by referring to the range where you want to count the filtered rows.

Function CountFilteredRows(rng As Range) As Long
    Dim cell As Range
    Dim count As Long
    count = 0
    ' Loop through each row in the range
    For Each cell In rng.Rows
        ' Check if the row is visible (not filtered out)
        If Not cell.EntireRow.Hidden Then
            count = count + 1
        End If
    Next cell
    ' Return the count of visible rows
    CountFilteredRows = count
End Function

Counting Filtered Rows from the Status Bar

The status bar is a handy tool located at the bottom right corner of the Excel window. It provides quick information about your worksheet, including the number of rows that meet your filter criteria.

First, you need to apply the filter to the data. To do this, select the data range, click the “Data” tab, and click “Filter”. Choose your filtering criteria from the drop-down list that appears.

Once the filter is applied, the status bar will automatically update to reflect the changes.

Here, you can see the number of visible (filtered) rows out of the total rows.

The status bar will display “54 of 100 records found”.

Here, 54 is the number of rows that meet your filter criteria, and 100 is the total number of rows. It lets you instantly see the number of filtered rows without using functions or formulas.

sample-file.xlsx

Final Thoughts

The ability to count filtered rows from the status bar simplifies data analysis in Excel. It allows you to quickly get an idea of how much of your data meets specific criteria. Combined with Excel’s other functions and features, this makes it a powerful tool for data management.