# Formula to Count the Number of Occurrences of a Text String in a Range

1. ## Formula to Count the Number of Occurrences of a Text String in a Range

Hi guys!

I am using this formula:

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")

What I need to do is to be able to target a cell and use the content of that cell in my "text" section, instead of actually typing the text.

Exemple:

=SUM(LEN(A5:A142)-LEN(SUBSTITUTE(A5:A142,"Shot by Guillaume Poulin","")))/LEN("Shot by Guillaume Poulin")

Let's assume I have:

A143 ; Guillaume Poulin
A144 ; Vincent Lecavalier
A145 ; Patrick Kane

My goal is that in my formula I can say "Shot by A143" instead of "Shot by Guillaume Poulin", so that I can refer to cells instead of actual text. That would prevent me from having to manually change the words.

That for the help!  Register To Reply

2. ## Re: Formula to Count the Number of Occurrences of a Text String in a Range

If the text string occurs only once per cell (if at all) I would suggest you revert to COUNTIF, eg: ``Please Login or Register  to view this content.``
If a string may appear multiple times per cell then I would use SUMPRODUCT over CSE array, eg: ``Please Login or Register  to view this content.``
But to reiterate the former if viable is certainly preferable  Register To Reply

3. ## Re: Formula to Count the Number of Occurrences of a Text String in a Range

The string does appear more then once. using sumproduct won't prevent me from having to manually change Guillaume Poulin to let say Vincent Lecavalier, instead of being able to just copie the formula to B:144 and having A144 automatically inserted.

sorry btw, i'm french so my english is sad...  Register To Reply

4. ## Re: Formula to Count the Number of Occurrences of a Text String in a Range

poug1903, apologies but I just recently updated the SUMPRODUCT example given I had initially failed to update so as to make dynamic (more fool me)... suggest taking another look.

In regard to a string appearing multiple times, just to be clear, I meant that "shot by xyz" could appear multiple times in any one given cell in your range - ie that string may appear twice in A10 ... if that is indeed the case then yes you should (IMO) adopt the SUMPRODUCT based approach.  Register To Reply

5. ## Re: Formula to Count the Number of Occurrences of a Text String in a Range

B148: =SUM(LEN(A5:A142)-LEN(SUBSTITUTE(A5:A142,"Shot by "&A148&"","")))/LEN("Shot by "&A148&"")

it works very well. the problem I encounter now is that if I copie paste it to B149

I still get:
A148 and not A149 in my formulas. Thanks for the update on sumproduct I'm trying this right away...  Register To Reply

6. ## Re: Formula to Count the Number of Occurrences of a Text String in a Range

I'm using your SUMPRODUCT formula and it works wonder, tho still the same problem. When I copie-paste, it doesn't chage my a143 to a144 in the formulas  Register To Reply

7. ## Re: Formula to Count the Number of Occurrences of a Text String in a Range

woot just works now!

THANKS SOO MUCH!!  Register To Reply

8. ## Re: Formula to Count the Number of Occurrences of a Text String in a Range

i need the above formula with example....  Register To Reply