Hi
I have a column of sentences in Column A, and a column of words in Column B. I wondered if there is a formula I can place in cell i Column C that will give med the number if times the word appears in Column A?
It would be best if it was not case sensitive.
I have attached an example file.
Thanks
Regards, Njaal, Norway
Try this
=COUNTIF($A$1:$A$7,"*"&B1&"*")
The monkey found the donkey rat The monkey screamed rats A monkey is dead donkey Some rats live in houses monkey Rats are afraid of donkeys No money for the rat All the money goes to the monkey
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
trouble with that it counts rat in all of rat rats and rattan
if you want a seperate count of whole words only so rats is 2 and rat is 1
=IF(B1="","",SUMPRODUCT(--ISNUMBER(SEARCH(" "&B1&" "," "&$A$1:$A$10&" "))))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks