How many different numbers in an array

[Guest]

I'm trying to determine how many different numbers (disregarding duplicates)
are in an array of numbers (a1.a400). Thanks.

 

[JW]

Ctrl+Shift+Enter
=SUM(IF(FREQUENCY(A1:A400, A1:A400)>0,1))

 

[T. Valko]

Try this:

=COUNT(1/FREQUENCY(A1:A400,A1:A400))

 

[Guest]

OR:
Ctrl+shift+enter
=SUM(1/COUNTIF(A1:A400,A1:A400))

 

[Peo Sjoblom]

It will fail if there are blank cells in the array, you could amend it like

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

but then it is less efficient that the other 2 formulas in this thread


--


Regards,


Peo Sjoblom