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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

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

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

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
You could also try:

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

Forum statistics

Threads
1,143,677
Messages
5,720,246
Members
422,272
Latest member
ginkgoVil

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
Top