Using a NOT modifier within Search()

[weeble33]

Hello

I have the following formula in place:

Please Login or Register  to view this content.

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

[weeble33]

I'm not so sure that my title, "Searching for everything but..." didn't describe my problem, but ok.

[icestationzbra]

i played around with a very small range of test data, and here is the formula for that:

Please Login or Register  to view this content.

the portion of interest for you is highlighted.

you asked for the ability to designate a bunch of values in the NOT section, which i am not able to achieve at the moment. this post will help bump up the thread and, i hope, a 'guru' will help you out; else, i will be attempting to do it anyway, and i will get you an answer (sooner or later :D)...

by the way, there is a downside to using SEARCH along with a string like "XL" in your attempt at exclusion; it will exclude XL, XLL, 5XLL, 8XL etc...

[weeble33]

Thank you for the reply, icestationzbra.

On one of my sheets, I have come to the same conclusion as you have. And I have also realized the fallibility of search() in exactly the examples you've shown. My data is organized in a way so that I can reduce some of those factors by preceding them with a dash:

Please Login or Register  to view this content.

This helps identify between al least "xl" and "5xl", but I still have the problem of "xll" being a valid term as you've pointed out. I've thought about using a space in the parameters "-xl " to make sure that it doesn't include the second "L", but as my example set above shows, very few entries contain a space after the size is listed. Ultimately, my current formula, which is probably 90-95% accurate, is this eye sore:

Please Login or Register  to view this content.

[icestationzbra]

i attempted something, which, i hope, helps you a wee bit. again, the same problem with "like" strings exists here, too.

i sincerely hope some 'guru' helps you out here, since i would also like to learn how to do this the right way.

[weeble33]

Yea, I triedd something like that once upon a time, but I can't get the multiple parameters to work out, which for yours seems to work for some but not others. Here's mine:

Please Login or Register  to view this content.

I removed "-xl" and "-5xl" from the list jsut to avoid the "like" strings. But for this I get an #N/A error and the weird part is that when doing a formula evaluation, the search parameters error out differently. The first 5 get #value errors and the last three get #N/A errors.

[tigeravatar]

weeble33,

I would use sumif instead of isnumber(search()):

Please Login or Register  to view this content.

The first sumif gets the total, the second sumif gets the total of items you want to exclude. So subtract the second from the first to get the desired result. Example attached.

Note that similar items will still cause issues. So -XLL will get excluded because it is similar to -XL.