Sumproduct multiple criteria

[Hoss97]

I am trying to add a second criteria to a sumproduct formula, and am struggling to get it right.

=SUMPRODUCT(--(ISNUMBER(SEARCH("*frame",$D$4:$D$75))),--($E$4:$E$75=O2),($C$4:$C$75))

This formula works as written, but I need to add a second word "set" for the d4:d75 range. So if it ends with frame or set, that it will return the totals. I'm sure this is an easy fix, but I can't figure it out. Thank you for any help.

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(((ISNUMBER(SEARCH("frame",$D$4:$D$75)))+(ISNUMBER(SEARCH("set",$D$4:$D$75)))),--($E$4:$E$75=O2),($C$4:$C$75))

 

[Hoss97]

That works perfectly...thank you very much. If I follow that same format, how many other criteria will I be able to add?

 

[Andrew Poulsom]

The + acts as OR, so you are only limited by the maximum for the length of a formula. Here's another (shorter) way:

=SUMPRODUCT(--(ISNUMBER(SEARCH({"frame","set"},$D$4:$D$75)))*($E$4:$E$75=O2)*($C$4:$C$75))

 

[Hoss97]

Thank you Andrew...knowing that the + acts as an or helps alot. I will play around with the shorter formula, and modify it as necessary. Thank you for your help. Problem Solved!