One way:

=SUMPRODUCT(--(X1:X1000="warranty"),
--(ISNUMBER(SEARCH("switch",AD1:AD1000))))


In article ,
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. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!