# 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. 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. But if you filter your data this function may not be suitable to count only 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) 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) 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)` Write the formula in Excel. And apply filters. 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. 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) ` Write the formula in Excel. For this example, use the Price column as a range.

Apply your suitable filter. You can use 102 as function_number as well

`SUBTOTAL(102,number_range)` Write the formula in Excel. It shows the number of visible rows.

These formulas will work for every filtered data. Feel free to filter or hide manually. Read More: How to Count Rows with Formula in Excel (5 Quick Methods)

#### 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)` 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. All rows are visible here, so it’s showing 10.

Hide or apply filters to the data set. We can use 3 in the behavior_option as well.

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

Write the formula in Excel. And apply filters. Here we have filtered for 3 books and it shows 3 in the Visible Rows column.

Use 5 in the behavior_option placeholder 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. Similarly, you can use 7 as the value of the behavior_option parameter. 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 out values applying a filter on different columns, the formula will give you the result properly.

Read More: How Excel Count Rows with Value (8 Ways)

### 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 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. Saving the code use this function in Excel. It counts the rows that are currently visible.

Hide manually or apply a filter to make a few of the rows visible. 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))` 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. It worked. Make changes in visible rows that match this value and check whether it works or not. 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. ### 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))` 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. 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. It works perfectly. It will also work fine if we use filter further. Read More: How to Count Rows with Data in Excel (4 Formulas)

## 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.

## Related Articles #### Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts 