Compare two columns row by row, count substring matches

[jokr1]

I want to compare columns A and B, row by row, where values in B might contain substrings of A.
How can I count the number of substring matches ("am" in hamster, "bit" in rabbit, "us" in mouse and "squirrel" in squirrel) with a formula? (Do not want to use column C at all to indicate if each row is a match or not)

I guess I have to use countifs or sumproduct? But don't know how to do it in this specific case.

 

[Fluff]

What version of Excel are you using?

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’)

 

[JoeMo]

Substitute SEARCH for FIND if you want to ignore case in your matches.

Book1
	A	B	C	D
1	frog	me
2	hamster	am
3	cat	e		matches
4	rabbit	bit		4
5	mouse	us
6	gorrila	hey
7	squirrel	squirrel
Sheet2 (2)

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT(--ISNUMBER(FIND($B$1:$B$7,$A$1:$A$7)))

 

[jokr1]

JoeMo, thanks! Used your search formula, works like a charm!

 

[JoeMo]

JoeMo, thanks! Used your search formula, works like a charm!

You are welcome - thanks for the reply.