# Is there an easy way to list the number of unique values in a column in LibreOffice calc?

In LibreOffice calc I have a column with thousands of values. Is there an easy way to list the number of UNIQUE values?

``````red
red
red
green
green
blue
blue
blue
``````

would return 3 because there are three unique entries.

Thanks

-
I flagged it, because it belongs on `Superuser`, imho. There is no buildin function, afaik, to do what you want. Maybe an extension somewhere? – user unknown Oct 14 '11 at 10:50
@userunknown I think it's fine; one of our on-topic bullets is "Applications packaged in *nix distributions" – Michael Mrozek Oct 14 '11 at 13:44

If it is a one-off, the easiest way to do this is to use a datapilot table and use that column as the row dimension. That will aggregate the list of values. Then you can just use functions like count if you want the number, or simple copy and paste if you want the unique list.

Using the datapilot table it would also be easy to just add a data element of count which would count the number of unique entries of each element.

-

Based on your Question, let's assume that the data shared above is in Column A: Hence,

``````   A
1 Red
2 Red
3 Red
4 Green
5 Green
6 Blue
7 Blue
8 Blue
``````

So, if you want the result to be measured in Cell A9, place the below function in A9

``````9 =SUMPRODUCT((A1<>"")/(COUNTIF(\$A\$1:\$A\$8,\$A\$1:\$A\$8)+(A1="")))
``````

You will get the answer in Cell A9

Remember: The Resulting Cell can be changed based on your requirement.

-
Welcome to Unix & Linux. Please read the help->tour. (especially the part about no-chit-chat). – Anthon Jan 23 '15 at 8:52
Could you explain why your code works exactly? – tschoppi Jan 4 at 11:51