how to fins sub strings with SUMPRODUCT?

[HolmesM]

Getting irritated that hacks that work with some formulas won't work with others.

case in point:
I can use "*"&A1&"*" to find a substring with SUMIFS, but not SUMPRODUCT.

with SUMPRODUCT:
this works: SUMPRODUCT(($B$4:$B$15=G15)*($C$3:$E$3=G16)*(C4:E15>0))
but not this: SUMPRODUCT(($B$4:$B$15="*"&G15&"*")*($C$3:$E$3=G16)*(C4:E15>0))

Please help if you can!

Thank you!

[Tony Valko]

Maybe this...

=SUMPRODUCT(ISNUMBER(SEARCH(G15,$B$4:$B$15))*($C$3:$E$3=G16)*(C4:E15>0))

[JohnTopley]

Try

=SUMPRODUCT((ISNUMBER(SEARCH(G15,$B$4:$B$15))*($C$3:$E$3=G16)*(C4:E15>0)))

[Pete_UK]

You can do it like this:

=SUMPRODUCT((ISNUMBER(SEARCH(G15,$B$4:$B$15)))*($C$3:$E$3=G16)*(C4:E15>0))

Hope this helps.

Pete

[HolmesM]

Thank you so much all!

I've tried all three - JohnTopley's works! Tony and Pete's close, just a matter of embedding.

Thank you again!!!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Tony Valko]

I've tried all three - JohnTopley's works! Tony and Pete's close, just a matter of embedding.

Hmmm...

I get the same results from all 3 formulas.