Formula to count the number of different values in a range

  • Thread starter Thread starter PCLIVE
  • Start date Start date
P

PCLIVE

I'm looking for a formula that will give me the number of different values
in a range.

Example: Column A may have five cells that are "4", five cells that are
"7", five cells that are "9". Of the fifteen cells that contain data, there
are only 3 different values. I'd like to use a formula that will count the
number of different values in column A, in this case the result is "3".

Thanks,
Paul
 
Try...

=SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))

OR

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

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Wow.

That's a more complicated formula that I expected. Nevertheless, it seems
to work.

Thanks for your help,
Paul
 
Back
Top