Excel Count Visible Rows (Formula and VBA Code)

To satisfy the demand of different circumstances you may need to count only the visible rows in Excel. Today we will show you how to count visible rows in Excel. The methods we are going to use will work on all the versions of Microsoft Excel.

Before diving into the big picture, let’s get to know about the workbook that is the base of our today’s examples.

Excel book - Excel Count Visible Rows

We have a table of three columns; Books, Genre, Author. There are 10 books listed here. In reality, real scenario your datasets may be more complex and larger than this.

For showing the count of visible rows we will apply filters on the table. This will hide a few rows and you can understand the differences.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Count Visible Rows in Excel

For counting rows you can use the COUNTA function. This function will count all the rows that are not empty.

COUNTA- Excel Count Visible Rows

But if you filter your data this function may not be suitable to count only visible rows.

COUNTA - result - Excel Count Visible Rows

Instead of showing the count of visible rows, this function shows all the rows inside the range.

So, we have understood that mere COUNTA function will not give us our desired result. We need some other approaches.

1. Count Using Formulas

1.1. Formula using SUBTOTAL Function

We can count visible rows using the SUBTOTAL function. Let’s see the syntax of the function first.

SUBTOTAL(function_number, range)

range: The range you want to subtotal.

function_number: This number specifies which calculation should be made. There are several predefined numbers for this placeholder.

Function Include hidden Ignore hidden
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV 7 107
STDEVP 8 108
SUM 9 109
VAR 10 110
VARP 11 111

These are the functions that can be done using the SUBTOTAL function. All you need to do is to insert the number for the function you want to use.

Since our agenda for today is counting rows, we will use the number of COUNT functions.

For text values we need to use the COUNTA function. So our function_number should be 3.

SUBTOTAL(3,range)

SUBTOTAL - Excel Count Visible Rows

Write the formula in Excel.

SUBTOTAL formula - Write the formula in Excel.

We are showing examples using the Books column within our range. You can choose any of the columns.

It gave the rows that are visible.

Now use filter (this will make some rows visible and some invisible)

Visible rows count subtotal and 3 - Excel Count Visible Rows

In our example, 3 rows are visible and our formula is also showing the same.

You can rewrite the formula using 103 as function_number.

SUBTOTAL(103,range)

SUBTOTAL 103-Excel Count Visible Rows

Write the formula in Excel. And apply filters.

SUBTOTAL 103 - Excel Count Visible Rows

In this example, we have filtered by Genre and 5 rows are visible. Our formula gave the correct result.

For showing you how you can make this function work on numbers, we have added an extra column Price. 

New column - Excel Count Visible Rows

You can use the COUNTA function for the numbers as well. But let’s get used to the COUNT function.

Generic formula will be something like this

SUBTOTAL(2,number_range) 

SUBTOTAL for numbers - Excel Count Visible Rows

Write the formula in Excel.

Numbers rows - Excel Count Visible Rows

For this example, use the Price column as a range.

Apply your suitable filter.

Number filter visible - Excel Count Visible Rows

You can use 102 as function_number as well

SUBTOTAL(102,number_range)

Subtotal 102-Excel Count Visible Rows

Write the formula in Excel.

SUBTOTAL 102 filter result - Excel Count Visible Rows

It shows the number of visible rows.

These formulas will work for every filtered data. Feel free to filter or hide manually.

SUBTOTAL 102 filter result - Excel Count Visible Rows

1.2. Formula using AGGREGATE Function

You can also count visible rows using the AGGREGATE function as well.

The syntax for the AGGREGATE function is as follows

AGGREGATE(function_number,behavior_options, range)

range: Range you want to aggregate.

function_number: This number specifies which calculation should be made.

behavior_options: Set this using number. This number denotes how the function will behave.

The AGGREGATE function does several tasks so numbers of functions are predefined within it. We are listing few frequently used function’s numbers

Function Function_number
AVERAGE 1
COUNT 2
COUNTA 3
MAX 4
MIN 5
PRODUCT 6
SUM 9
LARGE 14
SMALL 15

There are 8 possible values for behavior_options. They are

Value Behavior
0 Ignore SUBTOTAL and AGGREGATE functions
1 Ignore hidden rows, SUBTOTAL and AGGREGATE functions
2 Ignore error values, SUBTOTAL and AGGREGATE functions
3 Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error values

From these tables, you might have seen that for count (numbers or texts) we need to use 3 as function_number.

Since our intention here is to eliminate count for hidden rows, we have several options (4 in particular) for behavior_options place.

Our formula will be

AGGREGATE(3,1,range)

AGGREGATE - SUBTOTAL 102 filter result - Excel Count Visible Rows

1 stands for ignoring hidden rows, SUBTOTAL and AGGREGATE functions

Since we gave 3 in function_number it will count visible rows only. And along with ignoring hidden rows it will ignore SUBTOTAL and AGGREGATE functions (if any) as well.

AGGREGATE 3-1 - SUBTOTAL 102 filter result - Excel Count Visible Rows

All rows are visible here, so it’s showing 10.

Hide or apply filters to the data set.

Filtered Aggregate - SUBTOTAL 102 filter result - Excel Count Visible Rows

We can use 3 in the behavior_option as well.

AGGREGATE(3,3,range)

Aggregate 3-3-SUBTOTAL 102 filter result - Excel Count Visible Rows

3 will command Excel to ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions. 

Write the formula in Excel.

Aggregate 3-3 result - SUBTOTAL 102 filter result - Excel Count Visible Rows

And apply filters.

Filter - SUBTOTAL 102 filter result - Excel Count Visible Rows

Here we have filtered for 3 books and it shows 3 in the Visible Rows column.

Use 5 in the behavior_option placeholder

AGGREGATE 3-5-SUBTOTAL 102 filter result - Excel Count Visible Rows

This is also doing the same. Behavior option value 5 stands for ignoring hidden rows.

Earlier options (1 and 3) did the same, but that will take more time since their mechanism is such that they will also evaluate SUBTOTAL – AGGREGATE or Error values.

Apply different filters and make different numbers of rows visible to test your formula.

Filter visible - SUBTOTAL 102 filter result - Excel Count Visible Rows

Similarly, you can use 7 as the value of the behavior_option parameter.

Aggregate 3-7-SUBTOTAL 102 filter result - Excel Count Visible Rows

Value 7 ignores hidden rows and error values.

If you only need to ignore hidden rows it’s better to use 5, since it takes less time to execute and show results.

Filter visible - SUBTOTAL 102 filter result - Excel Count Visible Rows

Filter out values applying a filter on different columns, the formula will give you the result properly.

2. Count Using Macros

Using macros you can create your own function in Excel. Here we have a demo function to show you.

Function COUNTVISIBLE(range)
    Dim xCount As Long
    Dim xCell As range
    Application.Volatile
    For Each xCell In range
        If (Not xCell.EntireRow.Hidden) And (Not xCell.EntireColumn.Hidden) Then
            xCount = xCount + 1
        End If
    Next
    COUNTVISIBLE = xCount
End Function

Explore the Developer tab on Excel and click Visual Basic

Visual Basic -Excel Count Visible Rows

Or simply press ALT + F11. 

Microsoft Visual Basic for Applications window will pop out in front of you.

Click Insert > Module, and paste the VBA code to the Module window.VBA code - Excel Count Visible Rows

Saving the code use this function in Excel.

COUNTVISIBLE - Excel Count Visible Rows

It counts the rows that are currently visible.

Hide manually or apply a filter to make a few of the rows visible.

Filter - COUNTVISIBLE - Excel Count Visible Rows

It counts only the visible rows.

Count Visible Rows with Criteria

1. Criteria with OFFSET

To count visible rows that match given criteria you need to use a function called OFFSET. 

Alongside the OFFSET function, you need to use the SUMPRODUCT, SUBTOTAL, ROW, and MIN functions.

SUMPRODUCT(SUBTOTAL(3,OFFSET(range,ROW(range)-MIN(ROW(range)),,1)), --(range=keyValue))

OFFSET - Excel Count Visible Rows

We checked keyValue within the range and it returns an array of TRUE and FALSE. The double unary operator (--) coerces the TRUE and FALSE values into 1’s and 0’s.

Within the OFFSET function, we have a couple of ROW functions. Subtraction of 2 ROW functions provides an array of rows, starting from 0. This array will work as rows inside the OFFSET function.

Now we are setting a range, an array of rows, and 1 as height inside the OFFSET function. This will provide an array of entire values within the range.

The SUBTOTAL function converts the array returned by the OFFSET function into an array of 1’s and 0’s where 1s represent visible cells and 0s match hidden cells.

Now, the SUMPRODUCT function has two arrays of 1s and 0s. It multiplies the arrays and then calculates the sum.

Write the formula in Excel.

OFFSET formula - Excel Count Visible Rows

It worked. Make changes in visible rows that match this value and check whether it works or not.

Filter - OFFSET - Excel Count Visible Rows

This time we have only one Thriller book visible and the formula gives the result correctly.

If our visible rows don’t have the keyword, we are looking for the formula will show 0 as result.

Filter OFFSET-Excel Count Visible Rows

2. Criteria with INDIRECT

You can count rows that contain any specific keywords using the INDIRECT function as well.

Similar to the earlier formula you need to use the SUMPRODUCT, SUBTOTAL, ROW functions.

Let’s see the generic formula first.

SUMPRODUCT(SUBTOTAL(103,INDIRECT("column"& ROW(range))),--(range=keyValue))

INDIRECT - Excel Count Visible Rows

We checked keyValue within the range and it returns an array of TRUE and FALSE. The double unary operator (--) coerces the TRUE and FALSE values into 1’s and 0’s.

We have a ROW function inside the INDIRECT function. We need to set a column name within the INDIRECT function.

We have written “B” and inside the ROW function given the range B4:B13. This will give an array of entire values within these cells.

The SUBTOTAL function converts the array returned by the INDIRECT function into an array of 1’s and 0’s where 1s represent visible cells and 0s match hidden cells.

Now, the SUMPRODUCT function has two arrays of 1s and 0s. It multiplies the arrays and then calculates the sum.

INDIRECT formula - Excel Count Visible Rows

Here our Search Key was Thriller but no Thriller rows were visible, so it gave 0.

Change the Search Key and see whether it works fine or not for other values.

Change Key - Excel Count Visible Rows

It works perfectly. It will also work fine if we use filter further.

Filter indirect - Excel Count Visible Rows

Conclusion

That’s all for today. We have listed several ways to count visible rows in Excel. Hope this will be helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods to do the task which we have missed here.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo