SOLVED

count occurrences of text in a cell range

Copper 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

bob loves jane

jane

love

 

COUNTIF would return 1 but my formula should return 2

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@jgossage 

=COUNT(SEARCH(E3,C4:C7))

 

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.

 

@OliverScheurich 

 

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

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@jgossage 

=COUNT(SEARCH(E3,C4:C7))

 

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.

 

View solution in original post