How to count unique values in a column?

English
,
#1

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:

=UNIQUE(A2:A34)

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.

#2

Hallo

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

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

#3

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
#4

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):

=SUM(IF(ISBLANK(A2:A34);"";1/COUNTIF(A$2:A$34;A2:A34)))

with CTRL + SHIFT + ENTER

#5

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.