Count of Unique numbers in a column

[Rajkumar]

Hi,

I have some no's in a column,in which some are duplicating & some are
unique no's.Like

825
921
941
921
822
825
941
421

Now i want to count the unique no's in a column.
The result should be 5 for the above example.

Note : can the result be taken out in a cell instead of using an
another column.

 

[macropod]

Enter the array formula:
=SUM(IF(LEN(A1:A100),1/COUNTIF(A1:A100,A1:A100)))
to exclude 0 values, or as:
=SUM(1/COUNTIF(A1:A100,A1:A100)))
to include 0 values.

Change the range to suit your requirements.

Cheers
PS: Array formulae are entered with Ctrl-Shift-Enter instead on just Enter.

 

[Ron Rosenfeld]

Hi,

I have some no's in a column,in which some are duplicating & some are
unique no's.Like

825
921
941
921
822
825
941
421

Now i want to count the unique no's in a column.
The result should be 5 for the above example.

Download morefunc.xll and use the COUNTDIFF function:

http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefunc.xll.


--ron