# Counting unique rows in spreadsheets

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

``````=count(unique(A2:C5))
``````
• 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

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.

``````=COUNTUNIQUE(ARRAYFORMULA(A1:A&B1:B&C1:C))
``````
• 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