CODEWITHSUNDEEP
excel
jgozal
jgozal

Reputation: 1582

searching for multiple strings in a column excel

I've seen this question answered a few times but none of the solutions are working for me and I'd like to understand why.

I have the following:

=SUMPRODUCT(sheet1!$E$2:$E$228714,--(sheet1!$F$2:$F$228714="someword"), --(ISNUMBER(FIND({"word1","word2"},sheet1!$C$2:$C$228714))))

When in the third parameter of the function I only leave 1 word within the curly braces, it works great, but if I add "word2" in there, I get #VALUE! back.

I want to be able to FIND multiple words. Basically the third parameter would return 1 or 0 depending on whether it finds the specified words or not for every single cell in that row.

Upvotes: 1

Views: 323

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use SUMIFS(), with wildcards:

=SUM(SUMIFS(Sheet1!E:E,Sheet1!F:F,"someword",Sheet1!C:C,{"*word1*","*word2*"}))

Upvotes: 2

Related Questions