2

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

2
  • 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! Jan 2, 2013 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, 2017 at 20:19

4 Answers 4

4

In Google Spreadsheets:

=count(unique(A2:C5))
2
  • 2
    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. Jan 8, 2017 at 7:06
  • @AminMesbah touché. Nice answer. Upvoted
    – Joseph
    Jan 18, 2017 at 19:08
3

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

3
=COUNTUNIQUE(ARRAYFORMULA(A1:A&B1:B&C1:C))
1
  • That was also my solution. I like to keep things as easy to read as possible. Good Job.
    – Xzila
    Oct 5, 2017 at 20:17
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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