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

1. ## 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. ## 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.

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

Ok, here goes the sample sheet.

4. ## 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. ## 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. ## 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. ## Re: lookup multiple values of a single cell and return sum of results

That's great! Thanks a lot!

Zepp

8. ## 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.

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

#### 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