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[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mango[/TD]
[TD]2[/TD]
[TD]Steak[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Orange/Clementine/Mandarin[/TD]
[TD]3[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Peach/Nectarine/Apricot[/TD]
[TD]2[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pear[/TD]
[TD]2[/TD]
[TD]Honeydew[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apricot[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nectarine[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Watercress[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mango[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Clementine[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mango[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!