Get count of unique field values

[Ken]

Hi.

I have a table with a column called Employee ID. Many rows can have
the same Employee ID. I need to get the total number of unique
Empolyee IDs and use this number in a formula.

What's the best way to do this? I'm wondering if I should make a
Pivot table that provides this number, but I'm not sure how to do
that. Or perhaps there is some other approach.

Any info would be greatly appreciated!

Thanks,

Ken

 

[Frank Kabel]

Hi Ken
have a look at
http://www.cpearson.com/excel/duplicat.htm#CountingUnique
for some formulas depending on your data structure

 

[Peo Sjoblom]

One way

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

where the employee IDs are in A

 

[Ron de Bruin]

Try this array formula

Read this first
http://www.cpearson.com/excel/array.htm

=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))