Counting occurences of String Values using Cell reference

  • Thread starter Thread starter ashamishra
  • Start date Start date
A

ashamishra

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
 
Countif will do this simply;
next to your first list (say in A1:A5), put
COUNTIF(D$1:D$7,A1) -- where D1:D7 contains your second
list

and copy this formula down to the end of your first list (A2:A5 in this
example).
 
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
 
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!
 
Back
Top