count occurrences of text in a cell range

New Contributor

Excel 2016.  I would like to count the number of times a particular word appears in a range of cells, some of which contain more than one word.  COUNTIF only finds words if they are exact matches and no other words in a cell.  Example, find the word bob in the range of 4 cells below.



bob loves jane




COUNTIF would return 1 but my formula should return 2

2 Replies
best response confirmed by Hans Vogelaar (MVP)




Is this what you want to do? Enter above formula as matrixformula with ctrl+shift+enter if you don't work with Office365 or 2021.




That's it, exactly.  Funny that your solution did not come up when I searched for formulas.  Thanks much.