Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).