Count Strings Within Cell from List of Strings

[maertesa]

Hello,
I've learned a lot by lurking on this forum but haven't been able to find anything solving my question so decided to jump in.
I would like to search each cell in column V for items from a list in column Y. I would like column W to show me the number of times those items appear. Ideally, it would only count an item from the list once as shown in W5 below, but it wouldn't be the end of the world if it had to count orange twice.
Column V has a list of foods separated by commas. I would like a formula for column W that tells me how many times items from a separate list (Y) appear in the cell A next to it.

Excel 2010
	V	W	X	Y
1	Foods	Count		Fruit List
2	Apple, steak, pizza, orange	2		Apple
3	Beans, orange, Pizza	1		Banana
4	Beans, Pizza, Steak	0		Orange
5	Orange, Banana, Orange	2		Pear
6				Watermelon
7				etc
Sheet1

Thanks!
Sam

 

[Domenic]

Hi Sam,

Try...

W2, copied down:

=SUMPRODUCT(--(ISNUMBER(SEARCH(", "&$Y$2:$Y$6&",",", "&V2&","))))

For a case-sensitive search, replace SEARCH with FIND.

Hope this helps!

 

[maertesa]

Awesome! Thanks for the help! My actual text wasn't quite as clean as my example (didn't always have commas, etc), so I removed the extra commas/spaces from your formula, but it appears to be working with that modification.

Thanks! I love this message board!