Count how many times value appears in range

[mdsurf]

Hi! trying to count how many times a value appears in a range, but I'm stuck because I can't figure out how to count it if it repeats in a cell.

Example: How do I calculate how many times the word apple appears below:

Location	5/1/2020	5/2/2020	5/3/2020
1	apple	apple, oranges	apple, apple
2	banana	peach, apple	orange

The answer should be 5 but I can't figure it out, thank you in advance for the help!

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	E	F	G	H
13	Location	05/01/2020	05/02/2020	05/03/2020
14	1	apple	apple, oranges	apple, apple
15	2	banana	pineapples, apple	orange
16
17	apple	5
Main

Cell Formulas
Range	Formula
F17	F17	=SUMPRODUCT((LEN(" "&F14:H15&",")-LEN(SUBSTITUTE(" "&F14:H15&","," "&E17&",","")))/(LEN(E17)+2))+0