About a year ago I asked this question with success. Now I’m trying to find when a word and number are in the same row but different columns, such as with “racecar” and “1”. SUMPRODUCT doesn’t work with numbers.
Hi Levity, in your previous question data were already in different column.
=SUMPRODUCT(H2:H118=“racecar”;I2:I118=“shoe”)
=SUMPRODUCT(ISNUMBER(FIND(“racecar”;H2:H118));ISNUMBER(FIND(“shoe”;i2:i118)))
In the last versions, the interpretation of numbers as text and vice versa is stricter.
So with the first formula, you can enter the number without quotes, or force the range as text:
=SUMPRODUCT(H2:H118=“racecar”;I2:I118=1)
=SUMPRODUCT(H2:H118=“racecar”;I2:I118&""=“1”)
Doh, I didn’t think to try it without the quotation marks. Thanks for responding so quickly!
What about if I wanted to do “>1” ?
Replace the = with >, or with any of the comparison operators. In SUMPRODUCT() you have to do ordinary comparisons (not quoted operators), different than SUMIF() or COUNTIF(), in those the operators are part of the second function parameter.