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.



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.

|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.

|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
  • 1
    Could you explain why your code works exactly? – tschoppi Jan 4 '16 at 11:51

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.