Count duplicates once
 

I need to count how many different codes/numbers are in a list, and some of
them are duplicated. eg:

300001600
300001600
300002465
300001435
300005642

I need the result to show that there are 4 different numbers, rather than 5
in total.

Count duplicates once
 

If the codes/numbers are strictly numbers:

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

If they may be alphanumeric:

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

Biff

"Tinkerbell1178" wrote in message
...
I need to count how many different codes/numbers are in a list, and some of
them are duplicated. eg:

300001600
300001600
300002465
300001435
300005642

I need the result to show that there are 4 different numbers, rather than
5
in total.

hello,

i have the same problem. I used the formula you suggested but it only works if the data series is predefined. i have a column of data to count and they change from month to month. I tried

=SUMPRODUCT((A:A<"")/COUNTIF(A:A,A:A&""))

but it doesn't work. Any suggestions?