Counting Part of a Text String in a Range

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try:

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

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

Forum statistics

Threads
1,218,945
Messages
6,145,360
Members
450,611
Latest member
JodiWe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top