COUNTIF used finding words in multiple columns

I’m basically trying to find the condition when “racecar” and “shoe” are both in the same row, but in two different columns. Most of my searching has brought me to similar questions for Open Office, but with solutions that don’t appear to work.

I get Error 502 for this formula:


SUMPRODUCT has also been recommended, but I don’t think that’s what I’m looking for.

I think the function to do this SUMPRODUCT()


If this words are not alone in the cell then:


The use of ISNUMBER() is because FIND() return #VALUE! when find nothing.

If you need regular expression to search, then use SEARCH() instead of FIND()

Perfect. Thanks!

Is there a way to do this with number AND letters? Such as “1” replacing “shoe”.

Please Levity, open a new thread explaining what you want achieve.

This is what i was looking for. Thank you so much