Hello
I have the following formula in place:
=SUMPRODUCT(--ISNUMBER(SEARCH(B620&"-"&C620,Sheet1!$A$1:$A$6000)),Sheet1!$B$1:$B$6000,--ISNUMBER(SEARCH("SP",Sheet1!$A$1:$A$6000)))
Basically It searches Sheet1 A:A for all occurrences of a text string (B620&"-"&C620) plus the string "SP" and gives me the sum of corresponding values in sheet1 B:B. This function works for what I need, but now in another spot I need to do the opposite and find all instances with (B620&"-"&C620) that does NOT contain "SP". I also need to NOT contain a few other strings, ie. {"SP", "XS","S","L","XL"} and maybe a few others.
So, If I have the following (B620 is "BR5", C620 is "45WH") and the Do Not Find list is as described above then:
BR5-45WH-XL (SP1)............15
BR5-45WH-5XLL..................20
BR5-45WH-S.......................13
BR5-45WH-8XL....................17
BR6-49RB-5XLL...................22
I would want the result 37 (20+17). I was thinking that maybe using <> would work like SEARCH(B620&"-"&C620&<>{"SP", "XS","S","L","XL"} , Sheet1....etc), but that doesn't seem to work.
Thanks
Bookmarks