I've got a list of entries in a range in LO Calc. The max size of the range is 1 X 9, but may include blanks and/or duplicates. How can I extract a list of unique values automatically, so that my user doesn't have to use the filter function?

  • Does this q/a solve your issue? – tohuwawohu Dec 4 '14 at 18:38
  • @tohuwawohu - that would be a logical approach but the question asks about a way to do it without using a filter. – fixer1234 Dec 24 '14 at 7:33

I'm not sure if your 1×9 is one row × nine columns or vice versa. I chose the latter but it should not be very difficult to transpose this array formula solution.

        Unique Count in Calc

The array formula in C2 is,

=SUM(IF(ISERROR(($A$2:$A$10<>"")/COUNTIF($A$2:$A$10; $A$2:$A$10&"")); 0; ($A$2:$A$10<>"")/COUNTIF($A$2:$A$10; $A$2:$A$10&"")))

Array formulas require Ctrl+Shift+Enter rather than simply Enter. If you plan to fill this right or down, you should adjust the absolute cell and cell range references to allow either a column or a row to 'float' in a relative manner. Once entered correctly, you can fill right or down as necessary.


OO Calc's SUMPRODUCT function can do this without Ctrl+Shift+Enter with this formula.

=SUMPRODUCT(($A$2:$A$10<>"")/COUNTIF($A$2:$A$10&""; $A$2:$A$10&""))


The unique list can be gathered with a similar array formula.

        Unique list in OO CAlc

The array formula in D2 is,

=IF(ISNA(MATCH(0; IF($A$2:$A$10<>""; COUNTIF(D$1:D1; $A$2:$A$10&""); 1); 0)); ""; INDEX($A$2:$A$10; MATCH(0; IF($A$2:$A$10<>""; COUNTIF(D$1:D1; $A$2:$A$10&""); 1); 0)))

This requires Ctrl+Shift+Enter rather than simply Enter. Once entered correctly it can be filled down to gather all unique entries.

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.