Counting Part of a Text String in a Range

[SamBo1234]

Hi All,

I need some kind of formula which will allow me to count the number of occurances part of a text string is found within a range of mutiple text strings.

For Example:

I want to count the number of times "THIS WORKS" is found in a range of text strings down column A such as below.

A1: THIS WORKS WELL
A2: HI THIS WORKS WELL
A3: HELLO AGAIN THIS WORKS WELL VERY GOOD
A4: HOWDY HI THIS WORKS GOOD

So i would expect my formula to return the value of: 4

As "THIS WORKS" is shown 4 times in the range of data.

I can do this for characters but not for strings... any help chaps ????

Many Thanks
Sam

 

[NBVC]

Try:

=SUMPRODUCT(--ISNUMBER(SEARCH("This Works",A1:A4)))

you can replace the string, "This Works" with a cell reference housing the string.

 

[SamBo1234]

It Works.

Many Thanks,

Appreciate it.

 

[Oaktree]

You could also try:

=COUNTIF(A1:A4,"*This Works*")