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

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

Sort by » oldest newest most voted

Hallo

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

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

more

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

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