Ask Your Question
1

How to count unique values in a column? [closed]

asked 2017-03-05 12:50:36 +0100

dmhowcroft gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-26 21:17:17.782100

1 Answer

Sort by » oldest newest most voted
1

answered 2017-03-05 13:12:09 +0100

karolus gravatar image

Hallo

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

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

edit flag offensive delete link more

Comments

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.
VGio gravatar imageVGio ( 2019-08-03 12:54:56 +0100 )edit

For the second one, you could use the solution of MrProgrammer (https://forum.openoffice.org/en/forum...):

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

with CTRL + SHIFT + ENTER

jhoany_uzc gravatar imagejhoany_uzc ( 2020-04-06 12:16:48 +0100 )edit

Question Tools

2 followers

Stats

Asked: 2017-03-05 12:50:36 +0100

Seen: 17,122 times

Last updated: Mar 05 '17