Count of unique entries
 

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


--
slang
------------------------------------------------------------------------
slang's Profile: http://www.excelforum.com/member.php...o&userid=24847
View this thread: http://www.excelforum.com/showthread...hreadid=383967

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

--
HTH

Bob Phillips

"slang" wrote in message
...

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


--
slang
------------------------------------------------------------------------
slang's Profile:
http://www.excelforum.com/member.php...o&userid=24847
View this thread: http://www.excelforum.com/showthread...hreadid=383967

One way

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



Regards,

Peo Sjoblom

"slang" wrote:


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


--
slang
------------------------------------------------------------------------
slang's Profile: http://www.excelforum.com/member.php...o&userid=24847
View this thread: http://www.excelforum.com/showthread...hreadid=383967

On Fri, 1 Jul 2005 13:11:05 -0500, slang
wrote:


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

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,


--
slang
------------------------------------------------------------------------
slang's Profile: http://www.excelforum.com/member.php...o&userid=24847
View this thread: http://www.excelforum.com/showthread...hreadid=383967