RE: Counting groups of exact numbers in a huge list (column)

[pgiessler]

This formula has been a great help to me. Thanks for the post; however what
if the items have an alpha-numeric coding (like automobile license plates)?
Is there an easy way to count unique labels in a list when the data in in a
text format?

Thanks

"tjtjjtjt" wrote:

> If they are all numbers:
> =SUM(IF(FREQUENCY(A1:A11,A1:A11)>0,1))
>
> You can see this page for variations and details:
> http://support.microsoft.com/kb/q268001/
>
> The formula is an Array, so you should press Ctrl+Shift+Enter to finish it.
>
> tj
>
> "*Jarom*" wrote:
>
> > I need to know how I can write a formula to count a very big list of numbers
> > and return the total number of numbers that are different in the list. For
> > example:
> > 8800719
> > 8800718
> > 8800718
> > 8800717
> > 8800719
> > 8800715
> > 8800719
> > 8800715
> > As you can see there are 8 numbers in this list but of those 8 there are
> > only 4 different case numbers. So if I had a list of several hundred or
> > thousand case numbers like the ones above, and many of them had duplicates
> > somewhere in the list, how can I get excel to give me a total number of all
> > the similar case numbers?

[Bob Phillips]

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"pgiessler" <[email protected]> wrote in message
news:[email protected]...
> This formula has been a great help to me. Thanks for the post; however
what
> if the items have an alpha-numeric coding (like automobile license
plates)?
> Is there an easy way to count unique labels in a list when the data in in
a
> text format?
>
> Thanks
>
> "tjtjjtjt" wrote:
>
> > If they are all numbers:
> > =SUM(IF(FREQUENCY(A1:A11,A1:A11)>0,1))
> >
> > You can see this page for variations and details:
> > http://support.microsoft.com/kb/q268001/
> >
> > The formula is an Array, so you should press Ctrl+Shift+Enter to finish
it.
> >
> > tj
> >
> > "*Jarom*" wrote:
> >
> > > I need to know how I can write a formula to count a very big list of
numbers
> > > and return the total number of numbers that are different in the list.
For
> > > example:
> > > 8800719
> > > 8800718
> > > 8800718
> > > 8800717
> > > 8800719
> > > 8800715
> > > 8800719
> > > 8800715
> > > As you can see there are 8 numbers in this list but of those 8 there
are
> > > only 4 different case numbers. So if I had a list of several hundred
or
> > > thousand case numbers like the ones above, and many of them had
duplicates
> > > somewhere in the list, how can I get excel to give me a total number
of all
> > > the similar case numbers?