counting the number of times a word appears

[Guest]

I have over 1000 entries where one colum contains cells with multiple info
such as:
Provide professional development opportunities, Subsidise the cost of
training courses, Organise social events, such as presentation nights,
dinners, thanks BBQs or breakfasts,

Is there any simple way to count the number of times a word e.g.
'professional' appears in the 1000 cells in that column?

I know how to count if there is just one word or phrase in each cell, the
problem is occuring because there are multiple prhrases in each cell.

Any help you could provide would be great.

Cheers

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(A1:A1000) - LEN(SUBSTITUTE(A1:A100, "professional", ""))) /
LEN("professional")

or, if "professional was in B1:

=SUM(LEN(A1:A100) - LEN(SUBSTITUTE(A1:A100,B1,"")))/LEN(B1)

 

[JE McGimpsey]

Ooops - all the ranges should be the same length, e.g., A1:A1000

 

[T. Valko]

Here are some ideas:

H1 = some word

=COUNTIF(A1:A20,"*"&H1&"*")

However, this is susceptible to "false positives". For example, if the word
to count was profession the above formula would count professional.

This formula is more robust (but not "bulletproof") against "false
positives":

H1 = some word

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&H1&" "," "&A1:A20&" "))))

For example, if the word to count was profession and the string in the cell
contained profession, (profession<comma>) the above formula would not count
profession<comma>.

Also, do you want to count *every instance* of the word or just the number
of cells that contain the word? For example, profession appears in cell A1
twice. Should that count as 1 or 2? The formula to count every instance
would also be susceptible to "false positives".

 

[Guest]

Thanks so much for your help.

I was worried the answers would be extremely confusing, but I understood
yours and it worked. So thanks.

 

[T. Valko]

Glad it worked for you. Thanks for the feedback!