How to count unique values in a column?

Is there a built in function where I can specify a range of cells and get the number of unique values in those cells? For example, something like:


Or perhaps a function which returns the unique values so that I could then COUNT them?

I’ve already looked at this answer, but it looks like the solution to a much more difficult question.


=SUM( 1 / COUNTIF( A2:A34 ; A2:A34 ) )

entered as Array-formula with keys: <ctrl>+<shift>+<enter>

Good one. Two problems I’m having with these, however, are:

  • The formula seems to be very resource intensive when applied to thousands of raws (i.e. LO hangs for few minutes.
  • If there are any empty cells withing the range to be evaluated, COUNTIF considers them having a value appearing zero times and the whole formula results in a “DIV/0” error.
1 Like

For the second one, you could use the solution of MrProgrammer ([Solved] Count unique values in a list (line/row) (View topic) • Apache OpenOffice Community Forum):