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!