As a Google Sheets expert, I often come across the question of how to count unique values in multiple columns. While there isn’t a dedicated function for this in Google Sheets, don’t worry! I’ve got you covered with some nifty combinations of functions that will do the trick.
The Simple Method
In most cases, we can use the COUNTUNIQUE function in combination with ARRAYFORMULA to count unique values in multiple columns. Simply combine the columns you want to count and apply the formula.
For example, let’s say we have a dataset with first names in column B and last names in column C. We can use the following formula to count the unique combinations:
=ArrayFormula(countunique(B3:B&""&C3:C))Here’s what the result would look like:
In this example, we have a total of 8 rows (excluding the header row), but since the names “Anne Perez” and “Karen Bryant” repeat, the unique count is 6.
An Alternate Approach
If you prefer a different approach, you can use the UNIQUE, INDEX, and COUNTA functions to achieve the same result. Here’s how it works:
=counta(index(unique(B3:C),0,1))This formula also gives you the unique count of first and last names in columns B and C.
Counting Unique Multiple Columns Based on a Condition
Now, what if you want to count unique values in multiple columns based on a specific condition? Let’s say you want to extract employees who have taken advances above 100 and then count the number of unique employees.
We can achieve this by using the FILTER function. Here’s an example:
=filter(B3:C,D3:D>100)This formula filters out the rows that meet the condition.
To count the unique employees, we can use either the COUNTUNIQUE and ARRAYFORMULA combination or the UNIQUE, INDEX, and COUNTA combination.
For the COUNTUNIQUE and ARRAYFORMULA method, use the following formulas:
=byrow(filter(B3:C,D3:D>100),lambda(r,textjoin(" ",true,r)))
=countunique(byrow(filter(B3:C,D3:D>100),lambda(r,textjoin(" ",true,r))))For the UNIQUE, INDEX, and COUNTA method, use the following formulas:
=unique(filter(B3:C,D3:D>100))
=counta(index(unique(filter(B3:C,D3:D>100)),0,1))And there you have it! Now you know how to count unique values in multiple columns based on a condition in Google Sheets.
Thanks for reading, and if you want to learn more about merging columns or other Google Sheets tips and tricks, be sure to check out Crawlan.com.
