Counting occurrences of a word in cells in which there are multiple words

[Geri]

Hi folks,

So I know that "countif" will tell me how many pure instances of a word
there are in a cell range. For instance, in the following range,

Preferred fruit:

grape
apple
grape
peach

the countif function for grape would be 2.

But I would like to do something like this - given the column:

Preferred fruit:

grape
fig grape
apple
grape peach
peach pear

I would like to know a function that tells me that grape occurs 3 times
in the results, rather than 1. Countif appears to look for pure
occurrences (grape), not occurrences that are also mixed with other
text (fig grape).

Any ideas? Thanks! Geri (e-mail address removed)



------------------------------------------------




------------------------------------------------

 

[Darren Fall]

If you put an asterisk in front and behind the word grape
i.e. "*grape*", in your COUNTIF Function, this will do the
trick.

 

[Harlan Grove]

...
...

But I would like to do something like this - given the column:

Preferred fruit:

grape
fig grape
apple
grape peach
peach pear

I would like to know a function that tells me that grape occurs 3 times
in the results, rather than 1. Countif appears to look for pure
occurrences (grape), not occurrences that are also mixed with other
text (fig grape).

You've gotten two previous responses that almost get this right. They'd fail on
"grapefruit". The robust approach requires handling "grape" as a whole word.

=SUMPRODUCT(--ISNUMBER(SEARCH("* grape *"," "&PreferredFruit&" ")))