Counting Unique Values

  • Thread starter Thread starter Emma Hope
  • Start date Start date
E

Emma Hope

I'm hoping this is quite a simple one, i have a list of
numbers (which change and is very long) i.e.

1
3
57
57
83
83
83
934

and i need a formula to count the unique values i.e. in
this list there are 5 (1, 3, 57, 83, 934), blanks can be
ignored.

I'd appreciate any help on this. Thanks
Emma
 
Emma,

Try

=SUM(1/COUNTIF(A1:A8,A1:A8))

array entered (press and hold Ctrl + Shift while you press Enter)

I you do it correctly then Excel will put curly braces around the formula as
in:

={SUM(1/COUNTIF(A1:A8,A1:A8))}

HTH

Sandy
 
I dashed off that formula just as I was leaving to visit my daughter in
Pitlochry and didn't notice the "blanks can be ignored"

Cribbing off of Chip's page:

=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))

will do the job.

My apologies to the OP

Sandy
 
While the OP stated that the values were numerical a slight amendment to the
first formula
will take care of blanks

=SUM(IF(A1:A8<>"",1/COUNTIF(A1:A8,A1:A8)))

Note that the frequency formula will not work with text values
 
Just to complete the discussion, this is a non-array proposal:

=SUMPRODUCT((A1:A8<>"")/COUNTIF(A1:A8,A1:A8&""))
 
Back
Top