I've been searching around and can't quite get a formula to work. I am making a list of keywords for a subject and I want to count the number of times a word is found in a cell. I actually would like to search a range of cells like E:E rather than E2 but I would settle for the latter.

I have a tab for articles and paste values in E2, E3 etc

Keywords tab has my list of keywords like apple, banana, peach etc.

=COUNTIF(articles!E:E,B2) works and gives me a count of the cells that contain my keywords.

As I was working on this I made a formula that worked:

=SUM(LEN(articles!$E$2:$E$6)-LEN(SUBSTITUTE(Jobs!$E$2:$E$6,B2,"")))/LEN(B2)

B2 has the word I am looking for. This seems to be explicit to the value in B2. If B2 is "but" will the formula find both but and butter?