Count Unique Strings

I am trying to count "unique" items and I have tried the different
suggestions out there and itis still coming up incorrect.

What I have: (These are text values, and have to remain that way.)
00000002756
00000002766
00000002771
00000002771
00000039981
00000185817
00000195933
00000195933
00000195933
00000195933

So, my count should be 5.

I am using the formula:
=SUM(IF(FREQUENCY(MATCH(Range1,Range1,0),MATCH(Range1,Range1,0))>0,1))

And my total keeps coming up with 6.
What am I doing wrong?

Any help will be appreciated.
Thanks.
Stacy

Hi Stacey,
> So, my count should be 5.

I expect once you put it in writing and posted you saw that you
do have 6 not 5 unique entries

00000002756

00000002766

00000002771
00000002771

00000039981

00000185817

00000195933
00000195933
00000195933
00000195933
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<[email protected]> wrote in message news:[email protected]...
> I am trying to count "unique" items and I have tried the different
> suggestions out there and itis still coming up incorrect.
>
> What I have: (These are text values, and have to remain that way.)
> 00000002756
> 00000002766
> 00000002771
> 00000002771
> 00000039981
> 00000185817
> 00000195933
> 00000195933
> 00000195933
> 00000195933
>
> So, my count should be 5.
>
> I am using the formula:
> =SUM(IF(FREQUENCY(MATCH(Range1,Range1,0),MATCH(Range1,Range1,0))>0,1))
>
> And my total keeps coming up with 6.
> What am I doing wrong?
>
> Any help will be appreciated.
> Thanks.
> Stacy
>

Hi Stacy

>> So, my count should be 5.

6 is correct ?

You can also use this array formula (enter with Ctrl-Shift-enter)
=SUM(IF(LEN(D1:D10),1/COUNTIF(D1:D10,D1:D10)))

--
Regards Ron de Bruin
http://www.rondebruin.nl



<[email protected]> wrote in message news:[email protected]...
>I am trying to count "unique" items and I have tried the different
> suggestions out there and itis still coming up incorrect.
>
> What I have: (These are text values, and have to remain that way.)
> 00000002756
> 00000002766
> 00000002771
> 00000002771
> 00000039981
> 00000185817
> 00000195933
> 00000195933
> 00000195933
> 00000195933
>
> So, my count should be 5.
>
> I am using the formula:
> =SUM(IF(FREQUENCY(MATCH(Range1,Range1,0),MATCH(Range1,Range1,0))>0,1))
>
> And my total keeps coming up with 6.
> What am I doing wrong?
>
> Any help will be appreciated.
> Thanks.
> Stacy
>

I have looked at your data, and it looks like 6 to me.

This formula

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

also gives 6.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> I am trying to count "unique" items and I have tried the different
> suggestions out there and itis still coming up incorrect.
>
> What I have: (These are text values, and have to remain that way.)
> 00000002756
> 00000002766
> 00000002771
> 00000002771
> 00000039981
> 00000185817
> 00000195933
> 00000195933
> 00000195933
> 00000195933
>
> So, my count should be 5.
>
> I am using the formula:
> =SUM(IF(FREQUENCY(MATCH(Range1,Range1,0),MATCH(Range1,Range1,0))>0,1))
>
> And my total keeps coming up with 6.
> What am I doing wrong?
>
> Any help will be appreciated.
> Thanks.
> Stacy
>

Been a long day...
Was seeing the first two as one.
Sometimes you just need the moral support!
Thanks for responding so quickly though!

I think it's time to pack it in for today!!!!
: )


[email protected] wrote:
> I am trying to count "unique" items and I have tried the different
> suggestions out there and itis still coming up incorrect.
>
> What I have: (These are text values, and have to remain that way.)
> 00000002756
> 00000002766
> 00000002771
> 00000002771
> 00000039981
> 00000185817
> 00000195933
> 00000195933
> 00000195933
> 00000195933
>
> So, my count should be 5.
>
> I am using the formula:
>
=SUM(IF(FREQUENCY(MATCH(Range1,Range1,0),MATCH(Range1,Range1,0))>0,1))
>
> And my total keeps coming up with 6.
> What am I doing wrong?
>
> Any help will be appreciated.
> Thanks.
> Stacy