Excel 2007 : Looking up a text string within a database

[redave]

Hello All
I have a list of items (alpha numeric text) which will be contained within other items in a seperate database. Is there a way i can lookup and count how many time each one occurs? I have been trying various functions but have hit a brick wall....

For example i am trying to count how many times "ABC123" occurs within a list like the one shown below...

ITEM
abc123xyz
cba123xyz
123abcxyz
123cbaxyz
xyzabc123
111abc123
222abc123

any help will be much appreciated as my head hurts from bouncing of my desk...

Many Thanks

[oeldere]

=countif($A$1:$A$7;$A1)

and copy down.

[DBY]

Hello
I would try something like:

=COUNTIF(A1:A7,"*"&"ABC123"&"*")

or if you want it case sensitive:

=SUMPRODUCT((ISNUMBER(FIND("abc123",A1:A7)))*1)

I get the answer 4.

[redave]

Thanks for the reply but it does not work on my database. in the database i am looking up ABC123 the ABC123 does not appear in "A1" of the database - sorry i should have clarified

[redave]

Hi DBY/Anyone -
Sorry i have been checking the results and it is not working correctly i'm afraid as its not returning the correct amount of results.

The "ABC123" i am looking up in the 45K item databse is always located at the begning of the text string within the 45K items -does this make a difference?

Many Thanks