Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

e.g. a column which had


would return 3 because there are three unique entries.


share|improve this question
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.

share|improve this answer

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

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.

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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