I have two lists, first list is a fixed set of strings and has about 16
elements. The second list has zero or more occurences of the string
from the first list.

I would like to counf the number of occurences of each string from th
second list and report it against the first.

e.g.

First List contains

aat
activity
admin
als
cm

The second list contains
aat
aat
admin
admin
admin
admin
cm

I want the result to be

aat 2
activity 0
admin 4
als 0
cm 1

Thanks for help

--
Message posted from http://www.ExcelForum.com

Countif will do this simply;
next to your first list (say in A1:A5), put
COUNTIF(D$1$7,A1) -- where D17 contains your second
list

and copy this formula down to the end of your first list (A2:A5 in this
example).




"ashamishra >" <<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have two lists, first list is a fixed set of strings and has about 160
> elements. The second list has zero or more occurences of the strings
> from the first list.
>
> I would like to counf the number of occurences of each string from the
> second list and report it against the first.
>
> e.g.
>
> First List contains
>
> aat
> activity
> admin
> als
> cm
>
> The second list contains
> aat
> aat
> admin
> admin
> admin
> admin
> cm
>
> I want the result to be
>
> aat 2
> activity 0
> admin 4
> als 0
> cm 1
>
> Thanks for help.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

I have been trying this, but does not work. One of the things
I should mention is that my first list is a derived column , which is
using a concatenate function.

on sheet1 I have my second list

WAS.aat
WAS.aat
WAS.admin
WAS.admin
WAS.admin
WAS.cm

on sheet 2 I have my first list which is like
aat
activity
admin
als
cm
on Sheet 2 , I have added another column to use functio
=CONCATENATE("WAS.",A3) ( for cell B3) and copied this formula down
I added the third column for the COUNTIF which says
=COUNTIF(Sheet1!A$2:A$65,B3)

I am getting 0's even though I see this strings in the second list o
sheet 1

I do not have much experience in EXCE

--
Message posted from http://www.ExcelForum.com

Concatenated strings work fine in countifs. If I had to guess, I'd say there
are some unseen characters in one of the lists, is that possible?

Try this formula;

=SUMPRODUCT(--(TRIM(Sheet1!A$1:A$65)=TRIM(B3)))

where B3 contains your concatenated text string, e.g. "WAS.aat"

This does essentially what countif does, but removes any unprinting
characters (such as rogue spaces).

If this doesn't work for you, try it on a smaller range in one sheet until
you can get it to work, with data you know is OK (even concatenated will
work), then change the cell refs to sheet1!





"ashamishra >" <<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have been trying this, but does not work. One of the things
> I should mention is that my first list is a derived column , which is
> using a concatenate function.
>
> on sheet1 I have my second list
>
> WAS.aat
> WAS.aat
> WAS.admin
> WAS.admin
> WAS.admin
> WAS.cm
>
> on sheet 2 I have my first list which is like
> aat
> activity
> admin
> als
> cm
> on Sheet 2 , I have added another column to use function
> =CONCATENATE("WAS.",A3) ( for cell B3) and copied this formula down
> I added the third column for the COUNTIF which says
> =COUNTIF(Sheet1!A$2:A$65,B3)
>
> I am getting 0's even though I see this strings in the second list on
> sheet 1
>
> I do not have much experience in EXCEL
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>