Results 1 to 7 of 7

Using a NOT modifier within Search()

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Denver, CO
    MS-Off Ver
    Excel 2013
    Posts
    118

    Question Using a NOT modifier within Search()

    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
    Last edited by weeble33; 06-26-2012 at 12:37 PM. Reason: Title Change

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1