Formula for counting individual words.

[Marcílio_Lobão]

Good morning to all colleagues participating in this noble forum.

I have a seemingly simple question but I'm not getting success.
I would like to count on the usual kindness of all of you.

Question:
I have a list of fruit/dessert names, with several names in the cells.

I need to count how many times a certain fruit/dessert name appears in the interval.
Sometimes the name of the fruit is compounded into other names.

I want to count only individual names.

I'm using at E2 cell:

=SUMPRODUCT(LEN($A$1:$A$5)-LEN(SUBSTITUTE($A$1:$A$5 ;$C2; "")))/ LEN($C2)

Please, it is only possible to use formulas. No VBA in this Excel 2016 worksheet.

Any help will be greatly appreciated.

Have a great day.

 

[Fluff]

How about

=SUMPRODUCT(LEN(" "&$A$1:$A$5&",")-LEN(SUBSTITUTE(" "&$A$1:$A$5&"," ;" "&$C2&","; "")))/(LEN($C2)+2)

 

[Marcílio_Lobão]

Fluff, Good morning.

Thank You very much
Your suggestion solved my question.

Have a great weekend.

Greetings from Brazil

 

[Fluff]

You're welcome & thanks for the feedback.