To count unique values in visible rows, you cannot use the Countunique function in Google Sheets. It includes the hidden rows in the count.

I have a workaround to count unique values in visible rows in a column in Google Sheets.

You can make unwanted rows in your spreadsheet hidden in three different ways.

By using the Filter menu command, via the grouping of rows and also you can right-click and hide any individual rows.

Applying or finding a proper or suitable formula that skips hidden rows in a calculation is a nightmare for many Google Sheets users.

To solve this puzzle, I have recently published a tutorial that addresses some of the issues. Here is that tutorial – Subtotal Function With Conditions in Excel and Google Sheets.

In that tutorial, I didn’t include how to Countunique visible rows in Google Sheets. In this new Google Sheets tutorial, you can learn that tip. 

What I am going to detail in this tutorial is as follows.

1. How to count unique values in visible rows in a single column.
2. How to count unique values in visible rows in multiple columns.

You can learn more about these in the formula section.

What is Meant By Count Unique Values in Visible Rows

There are six unique values in the sample data in the D2:D10 range. You can see that in the below dataset.

I’ve applied a filter in the range D2:D10, and all the values are visible.

Assume I’ve filtered out some of the values.

The Countunique formula in cell G1 will return the same unique count result. There won’t be any changes.

=COUNTUNIQUE(D2:D10)

See how I want the Countunique to work in Google Sheets.

I want to skip the hidden rows in the count. Of course, the function Countunique doesn’t support it.

I know that you are keen to see the formula in cell D2.

Before sharing the formula, please understand that I am using a helper column to achieve this result.

When you check the above screenshot, you can understand that column E is hidden, which is my helper column.

The Formula to Count Unique Values in Visible Rows in a Column

As you may know, Google Sheets requires a helper column to perform calculations on visible rows. But Excel doesn’t need one.

I shared a link to one of my tutorials at the beginning. In that, I detailed the use of Subtotal in Excel and Google Sheets. You can read that to understand it.

Update:- Now, after the introduction of Lambda functions, We can skip using the helper column in Sheets too.

To Countunique only visible rows in Google Sheets, first, prepare a helper column as below (we can later remove it).

In cell E2, insert the below formula and drag it down until cell E10.

=subtotal(103,D2)

Alternatively, you can use the below Map Lambda formula in cell E2 only. It may spill down, so E3:E10 must be blank.

=map(D2:D10,lambda(r,subtotal(103,r)))

It is a Subtotal formula to count visible rows, not count unique visible rows. But I am not using this formula for that purpose.

This formula has a peculiarity. When you hide any rows, it returns 0 (zero) in the corresponding cells.

As an example, apply the formula =E5 in cell H1. Then hide row #5. The value in cell H1 would be 0.

Now we have a helper column. You can hide that column if you want.

In cell G1, apply the below Countuniqueifs formula that can count unique values in the range D2:D10 and skips values in hidden rows.

=COUNTUNIQUEIFS(D2:D10,E2:E10,1)

To avoid using the helper column, replace E2:E10 with the Map formula above.

=COUNTUNIQUEIFS(D2:D10,map(D2:D10,lambda(r,subtotal(103,r))),1)

Note:- Replace 103 with 3 to consider only filtered-out rows, not manually hidden or grouped rows.

Countunique Visible Rows in Multiple Columns in Google Sheets

Sometimes you may want to count unique values based on two columns.

It happens mostly with first and last names in two columns as follows.

Here my earlier formula only considers the first names and counts the unique records accordingly.

Then how to count unique rows considering values in two columns in Google Sheets.

We will use the Filter function to filter out hidden rows.

Then Unique the first and last names.

Take out one column using Index and use Counta to count it.

=counta(index(unique(filter(D2:E10,F2:F10=1)),0,1))

I hope I’ve well explained how to count unique values in visible rows in Google Sheets. Enjoy!

Related Reading:

1. Filter Unique Values Using the Filter Menu in Google Sheets.
2. How to Filter by Month Using the Filter Menu in Google Sheets.
3. How to Use COUNTIF with UNIQUE in Google Sheets.
4. Group and Average Unique Column in Google Sheets.