COUNTIF any part of cell string appears in range

[shella]

Hello! I'm having issues getting the correct formula to search a range and COUNTIF any part of a cell's text appears anywhere in the range.

Using A as the search criteria and C as the range, I would expect the column B results below, but, in B2/B3 it's returning 0 no matter what formula I try. Any suggestions from you smart folks is very much appreciated!

Thank you very much!

A

	A	B	C
1	Mango	2	Steak
2	Orange/Clementine/Mandarin	3	Pear
3	Peach/Nectarine/Apricot	2	Orange
4	Pear	2	Honeydew
			Apricot
			Nectarine
			Watercress
			Orange
			Mango
			Pear
			Clementine
			Mango

<tbody>
</tbody>

 

[Eric W]

Try this:

Book1
	A	B	C
1	Fruit	Count	List
2	Mango	2	Steak
3	Orange/Clementine/Mandarin	3	Pear
4	Peach/Nectarine/Apricot	2	Orange
5	Pear	2	Honeydew
6			Apricot
7			Nectarine
8			Watercress
9			Orange
10			Mango
11			Pear
12			Clementine
13			Mango
Name1

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(COUNTIF(A2,"*"&$C$2:$C$13&"*"))

 

[shella]

SOLVED: COUNTIF any part of cell string appears in range

Eric,

Thanks so much - that completely worked for the dummy data that I created but didn't give the intended result when I plugged into my actual spreadsheet because my actual range contains blank cells. HOWEVER, the "wrong result" shed enough light for me to realize I just needed to subtract the blank cells in my formula and it now works beautifully.

Thanks very much!!!

Best,
Shella

 

[Eric W]

Re: SOLVED: COUNTIF any part of cell string appears in range

Glad you got it figured out!