Count if word within a cell occurs in paragraph

[The Great SrH]

Hi all,

I'm looking for some help with a formula ideally (but if VBA is only option I'll accept that).


Basically I have a worksheet with data (called Data) in (Columns E to K contain comments boxes which may have lots of words in), and then another worksheet (called Words) with a list of 60 words.


I wan't to put a formula in the cell next to the word which will count the number of times the word in column A is used from Data Worksheet in columns E to K.

Something similar to

=COUNTIF(Data!E:K,A3)

The only issue is if the cell (A3) in Words worksheet says "Fair". This formula is searching the Data tab and returning cells which simply say "Fair".


I need it count the amount of times the word Fair is used across all columns (or even just Column E if thats easier).


It may be that a cell contains a lot of words like:
"Fantastic manager, she is always fair and will have an acceptable reason if she cannot do something, otherwise she'll go out of her way for everyone."


Thanks in advance!

 

[Peter_SSs]

If one of the words to check for is "fair" and a cell contains "John thinks it is fair but I don't think it is fair & Sam is fairly tired" should the count be 1, 2 or 3?

 

[The Great SrH]

Let's go with a count of 1, however it would be useful to know for 2 and 3!

 

[Peter_SSs]

Sorry, I should have asked this one too. Given this is not a "word" on its own and your somewhat indecisive answer to the last question, is this a count of zero or 1?
"Sam is fairly tired"

 

[The Great SrH]

Sorry, I should have asked this one too. Given this is not a "word" on its own and your somewhat indecisive answer to the last question, is this a count of zero or 1?
"Sam is fairly tired"

It's indecisive due to me wanting to learn the difference in methods, especially if I decide that the answer should be 2 or 3 (once having a play around with my Data).

"Sam is fairly tired" should count as 1 (I was using fair as an example, this shouldn't be much of an issue)

 

[The Great SrH]

Is anybody able to help?

 

[Peter_SSs]

Is anybody able to help?

Sorry, been away for a few days.

You were on the right track with COUNTIF. Suppose the first of the 60 words is in cell A2 of 'Words' then in B2 of 'Words' try this formula

=COUNTIF(Data!E$1:K$100,"*"&A2&"*")

Adjust the $100 to cover your data range (or expected data range) in 'Data', but I would advise not to use whole column references like you tried previously.
The formula can then be copied down.

 

[The Great SrH]

Sorry, been away for a few days.

You were on the right track with COUNTIF. Suppose the first of the 60 words is in cell A2 of 'Words' then in B2 of 'Words' try this formula

=COUNTIF(Data!E$1:K$100,"*"&A2&"*")

Adjust the $100 to cover your data range (or expected data range) in 'Data', but I would advise not to use whole column references like you tried previously.
The formula can then be copied down.

Thanks so much for this!

So is this formula for exact words eg. Fair (and wont return fairly)?

 

[Peter_SSs]

So is this formula for exact words eg. Fair (and wont return fairly)?

Simple way to find out would have been to try it.

However, the answer is "no" because you specifically said so.

"Sam is fairly tired" should count as 1

 

[The Great SrH]

Haha yeah fair enough.

So IF I did want it to only capture the exact word, how would I go about doing that?