+ Reply to Thread
Results 1 to 8 of 8

lookup multiple values of a single cell and return sum of results

  1. #1
    Registered User
    Join Date
    07-14-2011
    Location
    Brazil
    MS-Off Ver
    MS Office 2011 for MAC
    Posts
    4

    Question lookup multiple values of a single cell and return sum of results

    Hello folks.

    I am almost sure that someone might have asked the same thing, but I simply do not have time to look anymore as I have a lot of work to do.

    Here's what I am doing:
    I have one sheet where each line correspounds to a certain corporate objective (line 1 is "raise profits 10%", for example), and these objectives are composed by multiple goals(for instance, Objective 1 is composed by Goal1, Goal3 and Goal5) . These goals are listed in a diferent sheet. Each objective and each goal has a code (OB001, GL001, GL003,GL005).
    For each objective, there's a cell which contains concatenated all the codes of the goals that compose this objective(GL001GL003GL005).
    For each goal, there's a cell which contains it's status (0 for incomplete and 1 for complete).
    What I want to do is make a formula that detects when all goals that compose an objective are complete, and then automatically set that particular objective as complete too.
    The tricky thing is that the codes of the goals that compose the objective are all contained in a single cell, but I need to lookup each goal, return it's status then sum all of them and if the sum is equal to the number of goals, then the objective is complete.

    I don't know if I am being clear, or if it would help to post an example sheet.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: lookup multiple values of a single cell and return sum of results

    ...or if it would help to post an example sheet.
    It almost ALWAYS helps if you just show us. Your sample workbook should accurately depict the entire data structure and include several complete examples of the results you want to attain, mocked up manually. If it's not clear which cells you mocked up manually that you want us to help automate, point them out.

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    07-14-2011
    Location
    Brazil
    MS-Off Ver
    MS Office 2011 for MAC
    Posts
    4

    Re: lookup multiple values of a single cell and return sum of results

    Ok, here goes the sample sheet.
    Attached Files Attached Files
    Last edited by Zeppelin17; 08-09-2011 at 01:14 PM.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: lookup multiple values of a single cell and return sum of results

    In H3, put this formula, then copy the cell downward:

    =SUMPRODUCT(--(ISNUMBER(SEARCH(Requirements!$B$5:$B$19, Objectives!$F3))), Requirements!$D$5:$D$19)

  5. #5
    Registered User
    Join Date
    07-14-2011
    Location
    Brazil
    MS-Off Ver
    MS Office 2011 for MAC
    Posts
    4

    Re: lookup multiple values of a single cell and return sum of results

    Wonderful! Thank you!

    Now, I would like to understand a few things though. I can't figure out how is that "SEARCH" function working, as it is actually returning "#VALUE!". Then, what is that "SUMPRODUCT" doing, since, as far as I understand, the first array is actually not an array, but the result of the "ISNUMBER" function, which is "FALSE". So the "SUMPRODUCT" is multiplying "FALSE" with all of the cells in the other array? And what does that "--" do? Why the formula doesn't work if I take it out?

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: lookup multiple values of a single cell and return sum of results

    The SEARCH() function checks a string for the existing of a substring and returns either an error (not found) or the "position" of the substring in the main string.

    SEARCH(substring, mainstring)

    To get rid of the error, you couch that in an ISNUMBER test, which will then give you usable TRUE/FALSE answer instead.

    ISNUMBER(SEARCH(substring, mainstring))

    You can turn the TRUE/FALSE results into 0/1 results to be used in later math using the double unary operator --, this is a common technique you'll see inside of SUMPRODUCT() tests.

    --(ISNUMBER(SEARCH(substring, mainstring)))

    Now, so far, we are testing a single substring. By putting this into a SUMPRODUCT(), we can search a whole array of substrings against the mainstring and get an array back of substrings that equal=true=1.

    SUMPRODUCT(--(ISNUMBER(SEARCH(RANGE-of-substrings, mainstring))))

    Almost there, the reason we wanted to convert the true answers to 1 is so we could do math on another range, for your example.

    =SUMPRODUCT(--(ISNUMBER(SEARCH(RANGE-of-substrings, mainstring))), RANGE-to-Sum)

    So the array of 1s is mutliplied against the "range-to-sum" and the results added together. If you use the Evaluate Formula function on this formula and watch it unfold one calc at a time, you will see this process in action.

  7. #7
    Registered User
    Join Date
    07-14-2011
    Location
    Brazil
    MS-Off Ver
    MS Office 2011 for MAC
    Posts
    4

    Cool Re: lookup multiple values of a single cell and return sum of results

    That's great! Thanks a lot!

    Zepp

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: lookup multiple values of a single cell and return sum of results

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

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