How to Count Visible Rows in Excel (3 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

Basically, here we have a table with three columns; Books, Genre, and Author. There are 10 books listed here. In reality, in real scenarios, your datasets may be more complex and larger than this.

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)

Using COUNTA function to count visible rows in Excel

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

Using the SUBTOTAL Function to count visible rows 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 a filter (this will make some rows visible and some invisible)

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.

Using the SUBTOTAL Function in case of number to count visible rows in excel

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.

Utilizing the AGGREGATE Function to count visible rows in Excel

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.

Utilizing the AGGREGATE Function ignoring hidding rows to count visible rows in excel

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.

Utilizing the AGGREGATE Function ignoring error values to count visible rows in Excel

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.

Applying VBA Code to count visible rows in Excel

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

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

VBA code to Count visible rows in Excel

  • Now, put the following formula with the created COUNTVISIBLE function in the C16 cell.
=@COUNTVISIBLE(C5:C14)

COUNTVISIBLE function to count visible rows in excel

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.

Criteria with OFFSET Function to count visible rows in Excel

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.

Criteria with INDIRECT Function

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.

How to Count Visible Rows in a Filtered List in Excel

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

Using SUM function to count visible rows in Excel

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.

Practice Section


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo