searching for multiple strings in a column excel

Question

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.

Perhaps try replacing the comma after "word1" with a semicolon instead. Does this fix the problem? — ImaginaryHuman072889
– ImaginaryHuman072889, Commented Sep 14, 2017 at 16:20
I tried this as well but it just returns a 2 which is not the correct answer. What that 2 means I'm not sure.. — jgozal
– jgozal, Commented Sep 14, 2017 at 16:23

Scott Craner · Accepted Answer · 2017-09-14 16:26:23Z

2

Use SUMIFS(), with wildcards:

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

answered Sep 14, 2017 at 16:26

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

jgozal Over a year ago

yup, always wait a few mins. Thanks again!

jgozal Over a year ago

I'm having a bit of travel to translate your formula to a slightly different application. Would you be able to help me out with this one? SUMPRODUCT(sheet1!H$2:H$228714,sheet1!$E$2:$E$228714, --(ISNUMBER(FIND({"word1","word2"},sheet1!$C$2:$C$228714))))

jgozal Over a year ago

i think the issue with SUMIFS there is that it expects a criteria with its second argument

Scott Craner Over a year ago

I do not believe that SUMPRODUCT will do what you want. You can always SUM two SUMPRODUCTS, one that looks for the first and another that looks for the second.

Scott Craner Over a year ago

What application are you using. maybe another question stating the correct application will result in something you can use there.

|

Collectives™ on Stack Overflow

searching for multiple strings in a column excel

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related