How to Count Only Visible Cells in Excel (5 Tricks)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, in Excel, we need to count only visible cells. For instance, when you apply a Filter to Excel data, certain rows become hidden. Besides, while working in Excel, often we manually hide rows. In such cases, we may be required to count the visible number of rows. This article will guide you on how to count only visible cells.

Usually, we can use the COUNTA function to get the count of existing rows in a dataset. However, when rows are hidden manually or through applying the Filter option, the COUNTA function does not give the visible row count. So, I will show you the application of other Excel functions to get the count of only visible cells. To illustrate I have a dataset containing some food items’ sales data. Now, I will first hide the cells and show you how to count visible rows.

5 Tricks to Count Only Visible Cells in Excel

1. Using Excel SUBTOTAL Function to Count Only Visible Cells

We can use the SUBTOTAL function in Excel to count visible cells. First, I will apply a Filter to my dataset and then calculate the visible rows.

Steps:

  • First, select the dataset (B4:E13) and go to Data > Filter. Or you can press Ctrl + Shift + L to apply filtering in the dataset.

Excel SUBTOTAL Function to Count Only Visible Cells

  • As a result, the filtering drop-down icon is visible below.

Excel SUBTOTAL Function to Count Only Visible Cells

  • Then, I have filtered sales data for Corn Flakes (see screenshot). Now type the below formula in Cell C16 and press Enter from the keyboard.
=SUBTOTAL(3,B5:B13)

Excel SUBTOTAL Function to Count Only Visible Cells

  • Consequently, you will get the row count only for Corn Flakes which is 6.

Here, in the above formula, 3 tells the function what type of count to perform in the range B5:E13.

Note:

  • You can use the below formula to find the count of visible cells.
=SUBTOTAL(103,B5:E13)

2. Counting Visible Excel Rows Only with Criteria (Combination of Excel Functions)

This time, I will find the count of visible cells with criteria. For example, I manually hid row 11 of my dataset. Now I will calculate the visible count of rows containing Rolled Oats using a combination of Excel functions (e.g. SUMPRODUCT, OFFSET, SUBTOTAL functions). For your information, there are a total of 3 rows that contain Rolled Oats.

Get the Count of Visible Rows Only with Criteria (Combination of Excel Functions)

Steps:

  • In the beginning, type the following formula in Cell C18 and hit Enter.
=SUMPRODUCT((B5:B13=C16)*(SUBTOTAL(103,OFFSET(B5,ROW(B5:B13)-MIN(ROW(B5:B13)),0))))

Get the Count of Visible Rows Only with Criteria (Combination of Excel Functions)

  • Consequently, here is the cell count of visible cells for Rolled Oats.

🔎 How Does the Formula Work?

  • (B5:B13=C16)

The above part of the formula returns: {FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}

  • ROW(B5:B13)

Here, the ROW function returns the number of rows in the range B5:E13.

{5;6;8;9;10;11;12;13}

  • MIN(ROW(B5:B13))

Then the MIN function gives the smallest row in the range B5:E13.

  • (SUBTOTAL(103,OFFSET(B5,ROW(B5:B13)-MIN(ROW(B5:B13)),0)))

After that, the above part of the formula returns:

{1;1;1;1;1;1;0;1;1}

  • SUMPRODUCT((B5:B13=C16)*(SUBTOTAL(103,OFFSET(B5,ROW(B5:B13)-MIN(ROW(B5:B13)),0))))

Finally, the above formula returns {2}, which is the number of visible cells containing Rolled Oats.


3. Applying Excel AGGREGATE Function to Count Only Visible Cells

You can use the AGGREGATE function to find the count of visible cells. For instance, I will count the visible rows from the filtered dataset for Corn Flakes.

Steps:

  • First, type the below formula in Cell C15 and press Enter.
=AGGREGATE(3,3,B5:B13)

AGGREGATE Function in Excel to Count Only Visible Cells in Excel

  • As a consequence, you will get the count of visible rows only.


4. Combining COUNTA, UNIQUE, and FILTER Functions to Calculate Unique Visible Cells

Now, I will count the visible rows that contain unique values. To do that, I will use the combination of COUNTA, UNIQUE, and FILTER functions. We are going to use the above dataset where row 11 is hidden.

Steps:

  • Firstly, I have added an extra column ‘Visible’ to my dataset. I have used the below formula for the helper column.
=SUBTOTAL(3,B5)

Combination of COUNTA, UNIQUE, and FILTER Functions to Calculate Unique Visible Cells

  • Here, the extra column added above shows the visibility of the respective rows.
  • Then I calculated the total count of the visible rows by using the below formula:
=SUM(F5:F13)

Combination of COUNTA, UNIQUE, and FILTER Functions to Calculate Unique Visible Cells

  • Now comes the main part of this method. Type the below formula in Cell C17 and hit Enter.
=COUNTA(UNIQUE(FILTER(B5:B13,F5:F13)))

Combination of COUNTA, UNIQUE, and FILTER Functions to Calculate Unique Visible Cells

  • Lastly, the above formula will return the below result.

🔎 How Does the Formula Work?

  • FILTER(B5:B13,F5:F13)

In this part, the FILTER function filters all the food items that are visible and returns:

{“Corn Flakes”;”Rolled Oats”;”Corn Flakes”;”Mixed Nuts”;”Corn Flakes”;”Corn Flakes”;”Dry Fruits”;”Corn Flakes”;”Corn Flakes”}

  • UNIQUE(FILTER(B5:B13,F5:F13))

Then the UNIQUE function returns the unique food items from the filtered items which are:

{“Corn Flakes”;”Rolled Oats”;”Mixed Nuts”;”Dry Fruits”}

  • COUNTA(UNIQUE(FILTER(B5:B13,F5:F13)))

In the end, the COUNTA function returns the count of visible unique food items as below.

{4}

Note:

  • Remember you can use this formula only in Excel 2021 and Microsoft 365 as UNIQUE and FILTER functions are not available in older versions of Excel.

5. Joining Excel SUM, IF, ISNA, and MATCH Functions to Count Unique Visible Cells

Likewise the previous method, I will calculate the visible unique values in Excel using an array formula. In this method too, we will add a helper column to get the ultimate result. I will use the combination of SUM, IF, ISNA, and MATCH  functions in the formula. The formula I have used in this method was published in the Excel Expert Newsletter, issued on July 20, 2001 (no longer available).

Steps:

  • Firstly, I have used the below formula in the helper column. This formula is entered as an array (the result is outlined in blue color as below).
=IF(SUBTOTAL(3,OFFSET(B5:B13,ROW(B5:B13)-MIN(ROW(B5:B13)),,1)),B5:B13,"")

Excel Functions Combination to Show the Count of Unique Visible Cells

  • Then type the following formula in Cell C16 and press Enter.
=SUM(N(IF(ISNA(MATCH("",F5#,0)),MATCH(B5:B13,B5:B13,0),IF(MATCH(F5#,F5#,0)=MATCH("",F5#,0),0,MATCH(F5#,F5#,0)))=ROW(B5:B13)-MIN(ROW(B5:B13))+1))

Excel Functions Combination to Show the Count of Unique Visible Cells

  • Finally, you will find that there are four unique food items present in the visible rows of our dataset.

🔎 How Does the Formula Work?

This formula is quite lengthy, I have explained it in brief.

  • IF(ISNA(MATCH(“”,F5#,0)),MATCH(B5:B13,B5:B13,0),IF(MATCH(F5#,F5#,0)=MATCH(“”,F5#,0),0,MATCH(F5#,F5#,0)))

Initially, the above portion of the formula returns:

{1;2;1;4;1;1;7;1;1}

  • ROW(B5:B13)-MIN(ROW(B5:B13))+1)

Next, this part of the formula returns:

{1;2;3;4;5;6;7;8;9}

  • SUM(N(IF(ISNA(MATCH(“”,F5#,0)),MATCH(B5:B13,B5:B13,0),IF(MATCH(F5#,F5#,0)=MATCH(“”,F5#,0),0,MATCH(F5#,F5#,0)))=ROW(B5:B13)-MIN(ROW(B5:B13))+1))

In conclusion, the above formula returns:

{4}


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In the above article, I have tried to discuss several methods to count only visible cells in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


<< Go Back to Count Cells | 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.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo