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

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 purposedly. In such cases, we may require to count the visible number of rows. This article will guide you on how to count only visible cells.


Download Practice Workbook

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


5 Tricks to Count Only Visible Cells in Excel

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. 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 too to find the count of visible cells.
=SUBTOTAL(103,B5:E13)

Read More: Excel Formula to Count Cells with Text (Download Free Workbook)


2. Get the Count of Visible 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.

Read More: How to Count Blank Cells in Excel with Condition (3 Methods)


3. AGGREGATE Function in Excel to Count Only Visible Cells in Excel

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.

Read More: Count Empty Cells in Excel (4 Ways)


Similar Readings:


4. Combination of 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 have 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 filter 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.

Related Content: How to Count Blank Cells in Excel (5 Ways)


5. Excel Functions Combination to Show the Count of 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 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}

Read More: Excel Count Cells with Numbers (5 Simple Ways)


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.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo