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.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
3 Methods to Count Visible Rows in Excel
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, 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.
- 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.
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 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.
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.
Read More: How to Count Rows with Formula in Excel (5 Quick Methods)
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.
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.
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.
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.
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.
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.
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.
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.
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.
Read More: How Excel Count Rows with Value (8 Ways)
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.
- 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.
Finally, you will get your desired result.
Read More: How to Count Rows with VBA in Excel (5 Approaches)
How to Count Visible Rows with Criteria
Sometimes, you may need to count the rows that match your given 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.
- Initially, move to cell C19 and write down the formula.
C5:C14= The text value of “Genre”.
C16= The Seach Key “Detective”
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.
- Firstly, go to cell C20 and insert the formula.
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”.
Read More: How to Count Rows with Data in Excel (4 Formulas)
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.
- Firstly, go to cell F5 and enter the formula.
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.
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.
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
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. Visit our website Exceldemy, a one-stop Excel solution provider to find out diverse kinds of excel methods. Thanks for your patience in reading this article.