While dealing with a large dataset, adding many rows to your worksheet is necessary. But at the same time, you may need to hide some rows to give the worksheet a better outlook. To satisfy the demand of different circumstances, you may need to count only the visible rows in Excel. Here, in this article, we’re going to discuss how to count visible rows in Excel. So, let’s get started.

## How to Count Visible Rows in Excel: 3 Methods

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

Basically, here we have a table with three columns; ** Books**,

**, and**

*Genre***. There are 10 books listed here. In reality, in real scenarios, your datasets may be more complex and larger than this.**

*Author*Mainly, to show the count of visible rows, we will apply **filters** on the table. This will hide a few rows and you can understand the differences.

Not to mention, we have used the *Microsoft 365* version. You may use any other version at your convenience.

For counting rows, you can use the **COUNTA function**. This function will count all the rows that are not empty. To apply the formula, firstly, move to cell **E16** and insert the formula.

`=COUNTA(E5:E14)`

- Then, press
**ENTER**, and you will get the result like the image below.

Consequently, 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 the mere **COUNTA** function will not give us our desired result. We need some other approaches to count visible rows in Excel. Let’s explore!

### 1. Using the SUBTOTAL Function

To avoid the above consequences, we use the **SUBTOTAL function**. It takes a **func_num **to do the specific operation and then you have to insert the data range. There are different function numbers from which you can choose your specific number. In our case, as we want to count the visible rows, we will use the **COUNT **and **COUNTA **functions.

For text values, we need to use the **COUNTA** function. So our **function_number** should be **3**. Follow the steps to do it.

**📌**** Steps for Counting Visible Rows in Case of Texts:**

- Firstly, input the below formula in cell
**C17**.

`=SUBTOTAL(3,B5:B14)`

It checks for the **B5:B14 **cell and counts for the rows as we enter the* func_num *as

**3**for the

**COUNTA**function.

- We are showing examples using the
column within our range. You can choose any of the columns. It gave the rows that are visible. Now use a filter (this will make some rows visible and some invisible)*Books*

In this example, we have filtered by ** Books** and

**5**rows are visible. Our formula returned the correct result as you can see in the above image.

You can also use the **COUNT function** instead of the **COUNTA** function in the case of text values. Here the function number is **2**.

**📌**** Steps for Counting Visible Rows in Case of Numbers:**

- Firstly, go to cell
**C18**and insert the formula.

`=SUBTOTAL(2,E5:E14)`

The **SUBTOTAL(2, E5:E14) **syntax takes the * func_num *as

**2**where it stands for the

**COUNT**function, and then the data range as

**E5:E14**.

It also shows you the result of visible rows only. It doesn’t count the invisible rows.

### 2. Utilizing the AGGREGATE Function

You can also count the visible rows using the **AGGREGATE function**. The **AGGREGATE** function does several tasks, so the number of functions is predefined within it.

Since our intention here is to eliminate the count for hidden rows, we have several options (4 in particular) for the ** behavior_option** placeholder.

**Case 1: **

In the first, we’ll use **1** in the ** behavior_option** argument which is for ignoring hidden rows, and nested functions.

- First of all, move to cell
**C17**and enter the formula.

`=AGGREGATE(3,1, B5:B14)`

Here, **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.

As you can see, all rows are visible here, so the result is **10**.

**Case 2: **

In the second case, we’ll use * **3** in the **behavior_option** argument *which is ignoring hidden rows, and error values.

Initially, go to cell **C18** and write up the formula.

`=AGGREGATE(3,3, B5:B14)`

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

You may hide some of the rows to show differences. The count shows the result for the visible rows.

**Case 3: **

Now, we’ll use *5 **in the **behavior_option **argument *which is ignoring hidden rows

Primarily, go to cell **C19** and write up the formula.

`=AGGREGATE(3,5, B5:B14)`

This is also doing the same. The** 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.

You get your result after applying the formula and pressing **ENTER**.

**Case 4: **

In this case, we’ll use *7 **in the** behavior_option **argument *which is for ignoring hidden rows and error values.

Apply the below formula in cell **C20****.**

`=AGGREGATE(3,7, B5:B14)`

Value **7** ignores hidden rows and error values.

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

Thus, you will get the filtered rows and count for the visible ones.

### 3. Applying VBA Code

We apply the **VBA macros** to count the visible rows. The code counts for the visible rows automatically. It is an easy and time-saving task. Follow the below steps to do it.

**📌 Steps:**

- Firstly, go to the
**Developer**tab >> choose**Visual Basic**.

- Eventually, a window will appear. Select
**Insert**>> choose**Module**>> pick**Module1**.

- Now, write the VBA code in the
**General**box.

```
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
```

- Now, put the following formula with the created
**COUNTVISIBLE**function in the**C16**cell.

`=@COUNTVISIBLE(C5:C14)`

Finally, you will get your desired result.

## How to Count Visible Rows with Criteria

Sometimes, you may need to count the filtered rows that match certain criteria. Suppose you want to find rows with a specific word in them. So, for this case, you can use the **SUMPRODUCT**, **OFFSET**, **INDIRECT**, and **ROWS** functions to count visible rows for specific criteria.

### 1. Criteria with OFFSET Function

In addition, to count visible rows that match the 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.

**📌 Steps:**

- Initially, move to cell
**C19**and write down the formula.

`=SUMPRODUCT(SUBTOTAL(3, OFFSET(C5:C14, ROW(C5:C14)-MIN(ROW(C5:C14)),,1)), --(C5:C14=C16))`

Here,

**C5:C14**= The text value of “**Genre**”.

**C16**= The Seach Key “**Detective**”

**Formula Explanation:**

In the above formula, we checked the key value 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 **1’s** represent visible cells and 0s match hidden cells.

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

Finally, you will get the result like the above image. It counts the rows for our searching text “**Detective**”.

### 2. Criteria with INDIRECT Function

At this point, 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**, and **ROW** functions.

**📌 Steps:**

- Firstly, go to cell
**C20**and insert the formula.

`=SUMPRODUCT(SUBTOTAL(103, INDIRECT("B"&ROW(C5:C14))),--(C5:C14=C16))`

**Formula Explanation:**

Here, we checked the key value 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**” inside the **ROW** function given the range **B4:B13**. This will give an array of entire values within these cells.

Basically, 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.

Consequently, you get your result like the above image for the specific text “**Detective**”.

## How to Count Visible Rows in a Filtered List

You can also count visible rows by using the **SUM function**. This function simply adds the visible rows which are achieved by the **AGGREGATE** function already as we did earlier. Please follow the steps to do it.

**📌 Steps:**

- Firstly, go to cell
**F5**and enter the formula.

`=AGGREGATE(3,5,B5)`

Basically, this function counts the number of visible rows, which is **1** in our case.

- Then, press
**ENTER**and drag down it for other visible rows to find the output just like in the image below.

- At this moment, move to cell
**F16**and insert the formula.

`=SUM(F5:F13)`

This **SUM(F5:F13)** syntax calculates the value from **F5:F13** cells, which is the visible rows in our worksheet.

Eventually, you will get the result after pressing **ENTER**.

## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

**Download Practice Workbook**

Download the following practice workbook. It will help you to realize the topic more clearly.

## Conclusion

That’s all about today’s session. And these are some easy methods to count visible rows in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.

**<< Go Back to Count Rows | Formula List | Learn Excel**