COUNT number of times 2 or more values appear in a cell

[uk747]

Hi

Need to count how many times the word cat and red appear in list below Range (B2:B8) Result should be 3. The word can appear any where in the cell
Result should be 3

doesn't matter if cat appears within a word as long as it appears its fine i.e. scatty red dog would count as well

cat sheep dog

blue dog black

cat man red

red cat

cat blue

dog red

cat badger red

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1
2	cat sheep dog		cat	3
3	blue dog black		red
4	cat man red
5	red cat
6	cat blue
7	dog red
8	cat badger red
Main

Cell Formulas
Range	Formula
D2	D2	=SUM((ISNUMBER(SEARCH(C2,A2:A8)))*(ISNUMBER(SEARCH(C3,A2:A8))))

 

[Peter_SSs]

Another possibility

22 07 26.xlsm
	A	B	C	D
1
2	cat sheep dog		cat	3
3	blue dog black		red
4	cat man red
5	red cat
6	cat blue
7	dog red
8	cat badger red
Count

Cell Formulas
Range	Formula
D2	D2	=COUNTIFS(A2:A8,"*"&C2&"*",A2:A8,"*"&C3&"*")

 

[uk747]

Thanks Both

 

[Fluff]

Glad we could help & thanks for the feedback.