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

[Peo Sjoblom]

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
>
>

[Bob Phillips]

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

--
HTH

Bob Phillips

"slang" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Ron Rosenfeld]

On Fri, 1 Jul 2005 13:11:05 -0500, slang
<[email protected]> 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

[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,