Is it possible to identify one of many strings of text in a given cell

[Hollywoood]

Hi all,

I recent received some help in writing a formula to identify a given string in a cell containing many strings. The result is below.

=COUNTIF('Sheet'!M:M,"*"&A2&"*")

I have been trying to expand on this but to no positive result. My goal is to be able to look through the same column, containing the multiple strings, and return either a true/false or a count of the occurences if, any of several separate strings exist in that cell.

I tried the very simple expansion to (which I realize is way wrong)

=COUNTIF('Sheet'!M:M,"*"&A2:A15&"*")

would i need to use a nested OR in the countif such as

=COUNTIF('Sheet'!M:M,OR("*"&A2&"*","*"&A3&"*",.....) ?? This doesnt seem to produce the correct result either

I am becoming increasingly owing to you folks lol.. as always thanks in advance

 

[Jonmo1]

Try this..

=SUMPRODUCT(COUNTIF('Sheet'!M1:M100,"*"&A2:A15&"*"))

I recommend NOT using the entire column refs like M:M, so I changed it to M1:M100


Hope that helps.

 

[wigi]

For instance, this works for me:

=SUM(COUNTIF(A1:H18,"*"&M1:M2&"*"))

Note that I changed the cell references.

Confirm the formula with Ctrl-Shift-Enter instead of Enter.

 

[Hollywoood]

Thanks gents, works like a charm!