Test string search in an array

[weeble33]

Hello,

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

Please Login or Register  to view this content.

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.

[Marcol]

Maybe?

Please Login or Register  to view this content.

Better you post a sample workbook showing some typical data and the expected result.

[weeble33]

Nevermind, I found my solution

Please Login or Register  to view this content.

Just needed ISNUMBER() modifier...and the -- coersion