How do you run the COUNTUNIQUE function on several columns?

Running COUNTUNIQUE(A1:C3) gives me 8, because there are 8 unique values, I want it to return 3 because I have 3 unique rows.

| A  | B  |  c  |
| a  | 1  |     |
| b  | 2  | foo |
| b  | 2  | foo |
| c  | 3  | bar |

P.S. I use Google spreadsheets, but I guess it's the same in Excel and LibreOffice Calc

  • Did you find the answer of @joseph4tw useful? I know I did. Perhaps you can flag it as such and let other people benefit from your Q&A! – Jacob Jan Tuinstra Jan 2 '13 at 8:13
  • See mquan86 for a easier to understand solution. BillN also suggested this but didn't explicitly write out a formula for you. – Xzila Oct 5 '17 at 20:19

In Google Spreadsheets:

|improve this answer|||||
  • This only works by accident. See my answer for a more robust solution. This appears to work for the particular example provided, but not for the right reasons. COUNT() returns the number of numeric values in a range. It returns 3 in this case because there are 3 numeric values. Try replacing the 3 in the example data with an 'A' and you will see what I mean. This formula will return a 2, which is not the number of unique rows. – Amin Mesbah Jan 8 '17 at 7:06
  • @AminMesbah touché. Nice answer. Upvoted – Joseph Jan 18 '17 at 19:08

The quickest way I can think of is to concatenate the three vaules into column D and do the CountUnique on Column D

|improve this answer|||||

After spending several hours on this problem, I've finally found a solution:

=ArrayFormula(QUERY(UNIQUE(A1:C4), "select count(Col1) label count(Col1) ''"))

UNIQUE(A1:C4) returns the unique rows in the range:

  • a, 1,
  • b, 2, foo
  • c, 3, bar

Then QUERY() treats those rows as a new range and counts how many items there are in the first column, which ends up being the number of unique rows: 3. The label at the end of the query just gets rid of the header, 'Count' that it would normally return.

There should really be a much simpler way to do this, but this method has proven to be adequate for my purposes, and adaptable to several similar problems.

|improve this answer|||||
|improve this answer|||||
  • That was also my solution. I like to keep things as easy to read as possible. Good Job. – Xzila Oct 5 '17 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.