Count of unique entries

[slang]

How do I count or filter for the number of unique numbers or names in a
column?
I have a list of 20,000 barcodes that repeat 2-7 times each in a column
A and want to know how many unique bar codes there are.

thanks

 

[Bob Phillips]

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

 

[Guest]

One way

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



Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

How do I count or filter for the number of unique numbers or names in a
column?
I have a list of 20,000 barcodes that repeat 2-7 times each in a column
A and want to know how many unique bar codes there are.

thanks

If there are no blanks, this *array* formula may work:

=SUM(1/COUNTIF(A1:A20000,A1:A20000))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

If there may be blanks in the data, try this array formula:

=SUM(COUNTIF($A$1:$A$20000,$A$1:$A$20000)/IF(
NOT(COUNTIF($A$1:$A$20000,$A$1:$A$20000)),1,
COUNTIF($A$1:$A$20000,$A$1:$A$20000))^2)


--ron

 

[slang]

Many thanks to all three of you. You guys rock! I'll be back for more
help if i need it, but hopfully the books i just bought and the class i
signed up for will take care of me...(just lost my #1 excel guy and now
am totally lost). You're livesavers.

-slang,