Test string search in an array

Hello,

To begin, I have these formulas which should help understand what I'm trying to do but gives me a #VALUE error:

=SUMPRODUCT(search(B19&"-"&C19,Sheet1!A:A),search("SP",Sheet1!A:A),Sheet1!B:B)
=SUMPRODUCT(B19&"-"&C19&"*SP*", Sheet1!B:B)

Now, I think my problem is that I can't use search(), find(), or wildcards in an array function like sumproduct. I have a feeling match() is the key, but I'm not sure how to implement it.

What I'm trying to do is combine the text strings in Columns B and C and search for that string in Sheet1 A:A, as well as "SP" that will be somewhere within the string. If those conditions are met, return the sum of all those instances from Sheet1 column B.

For example, I have "BR3" in B19 and "45WH" in C19. In Sheet1A:A there are 3 cells that have the strings "BR3-45WH-4XL (SP)", "BR3-45WH-4XL (SP2)","BR3-45WH-4XL (SP3)". Each of those has a numeric value in column B that I want added up. and returned.

Hope I explained this ok, let me know if further explanation is needed.