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.

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.

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

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

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

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.

Steps:

• In the beginning, type the following formula in Cell C18 and hit Enter.

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

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

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

Similar Readings

• How to Count Odd and Even Numbers in Excel (3 Easy Ways)
• Count Number of Cells with Dates in Excel (6 Ways)
• Excel Count Number of Cells in Range (6 Easy Ways)
• Excel VBA to Select First Visible Cell in Filtered Range

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.

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

• Now comes the main part of this method. Type the below formula in Cell C17 and hit Enter.

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

• Then type the following formula in Cell C16 and press Enter.

• 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

• How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)
• Count Filled Cells in Excel Using VBA (7 Methods)
• How to Count Number of Cells with Dates in Excel (6 Ways)
• Average Only Visible Cells in Excel (3 Quick Methods)
• Count Cells that Contain Specific Text in Excel
• How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
• How to Count If Cell Contains Number (Easiest 7 Ways)