As far as I know, there is no dedicated function to count unique values in multiple columns in Google Sheets. But it’s not that complicated using combinations of functions. In most cases, we can use the COUNTUNIQUE function with ARRAYFORMULA to count unique values in multiple columns in Google Sheets. It’s by combining the columns in question. But it won’t work in all scenarios, especially when the function argument is an expression (output of another function). Before elaborating, let’s start with the simple way of counting unique values in multiple columns in a dataset. The best example will be first names and last names in two columns.
Đếm số lần xuất hiện duy nhất của Tên và Họ trong các cột
We can use the following formula to count the unique first and last names in B3:C.
=ArrayFormula(countunique(B3:B&""&C3:C))
In the above list, as you can see, there are data in 8 rows that exclude the header row. Since the name Anne Perez and Karen Bryant repeat, the unique count will be 6.
How does this formula work?
- Syntax: COUNTUNIQUE(value1, [value2, …])
- We have used the value1 part of the argument, which is the first value or range to consider for uniqueness.
- In our case, it’s not the first value but the first range because we have combined two columns using the ampersand.
An alternate way, or the perfect way to do the same, is by applying UNIQUE with INDEX and COUNTA.
=counta(index(unique(B3:C),0,1))Note: If the first and last names are in two distant columns, you can follow the below syntax/formula where we can specify the columns separately.
=counta(index(unique({B3:B,C3:C}),0,1))The UNIQUE function returns the unique rows. Using the INDEX function, we extracted one column from that multi-column output. The COUNTA returns the count of the values in that column. This way, we can count unique values in multiple columns in Google Sheets.
Đếm số giá trị duy nhất trong nhiều cột dựa trên một điều kiện
In the following example, we will learn to count unique values in multiple columns when the argument is an expression. We will filter out some rows in a table and use that result to count. For example, I want to extract employees who have taken advances above 100. Then count the number of (unique) employees.
The following FILTER formula will do the first part, which is the conditional part.
=filter(B3:C,D3:D>100)
How to use this expression in COUNTUNIQUE & ARRAYFORMULA or the UNIQUE, INDEX & COUNTA combinations?
COUNTUNIQUE & ARRAYFORMULA:
We are unable to use the ampersand sign here to combine the first and last names since we have an expression, not individual columns. So we will use an alternate method to combine columns in the expression, which I explained in one of my earlier tutorials – The Flexible Array Formula to Join Columns in Google Sheets.
=byrow(filter(B3:C,D3:D>100),lambda(r,textjoin(" ",true,r)))Now apply the COUNTUNIQUE.
=countunique(byrow(filter(B3:C,D3:D>100),lambda(r,textjoin(" ",true,r))))UNIQUE, INDEX & COUNTA:
It is similar to using physical columns. You need to apply the UNIQUE to the filtered output.
=unique(filter(B3:C,D3:D>100))Then take out the first column using INDEX and apply the COUNTA.
=counta(index(unique(filter(B3:C,D3:D>100)),0,1))That’s all about how to count unique values in multiple columns in Google Sheets. Thanks for the stay. Enjoy!
Related: Count Unique Values in Visible Rows in Google Sheets
