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!

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...

based on your reply I used this formula:
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...

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

woot just works now!

THANKS SOO MUCH!!

i need the above formula with example....

kindly advice