Rog wrote...
I need to use two columns and search for two things and count
how often the two occur together. In col X will occur the word
"warranty" and in column AD will occur, for example the word
"switch". The problem is that the word switch will occur in a
paragraph, so I need a wild card and SUMPRODUCT does not support
this. . . .

FWIW, only SUMIF, COUNTIF, SEARCH, MATCH and {V|H}LOOKUP support
wildcards, and the last 3 only for exact matching. However, if you're
looking for a particular word that would be separated from other text
by spaces, you don't need wildcards.

=SUMPRODUCT(COUNTIF(Range,{"test *","* test *","* test"}))

and

=SUMPRODUCT(--ISNUMBER(SEARCH(" test ",Range)))

return the same result. The array argument to COUNTIF in the first
formula is necessary to capture "test" appearing at the start or end
of each cell value in Range as well as appearing in the middle of the
string. Eliminating the spaces would mean you could match "test" as a
substring of other words, e.g., "detested". So for more rigorous
matching, SUMPRODUCT/ISNUMBER/SEARCH is actually simpler to use. And
as an added bonus, SEARCH allows you to use wildcards if you have to.