Count Unique Values in Visible Rows in Google Sheets

Have you ever tried counting unique values in visible rows in Google Sheets? It’s a bit tricky because the Countunique function includes hidden rows in the count. But don’t worry, I have a workaround that will solve this problem for you!

How to Count Unique Values in Visible Rows in a Single Column

To count unique values in visible rows in a single column, follow these steps:

1. Hide Unwanted Rows: You can make unwanted rows in your spreadsheet hidden in three different ways. You can use the Filter menu command, group rows, or right-click and hide individual rows.

2. Create a Helper Column: In Google Sheets, we require a helper column to perform calculations on visible rows. In cell E2, insert the formula =subtotal(103,D2) and drag it down until cell E10. This formula will count visible rows and return 0 for hidden rows.

3. Count Unique Values: In cell G1, apply the formula =COUNTUNIQUEIFS(D2:D10,E2:E10,1). This formula will count unique values in the range D2:D10 and skip values in hidden rows.

If you want to avoid using the helper column, you can replace E2:E10 with the map formula map(D2:D10,lambda(r,subtotal(103,r))).

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

Count Unique Values in Visible Rows in Multiple Columns

Sometimes, you may want to count unique values based on two columns. For example, if you have first names and last names in two columns, you might want to count unique records based on both columns.

Here’s how you can do it:

1. Filter Hidden Rows: Use the Filter function to filter out hidden rows.

2. Unique the First and Last Names: Use the Unique function to get the unique combinations of first and last names.

3. Count the Unique Values: Use the Counta function to count the unique values in the filtered range. Apply the formula =counta(index(unique(filter(D2:E10,F2:F10=1)),0,1)).

That’s it! You now know how to count unique values in visible rows in Google Sheets.

For more tips and tricks on Google Sheets, check out Crawlan.com. Happy counting!