# vLookup the sum of multiple values from multiple criterias

1. ## vLookup the sum of multiple values from multiple criterias

Please see attached. If anyone could solve this, it would be greatly appreciated!

The highlighted cells in Summary tab should show the total training hours spent per person per activity pulled from the Data tab.
i.e. for Adam/Basketball, the cell should show 9 (from 4+5); David/Tennis = 3 (from just 3); Michael/Swimming = 16 (from 4+3+1+8), etc.

For those that don't appear i.e. Sherry/Swimming, it should show a sum of 0 instead of N/A if possible.

Not sure how to use the combination of vLookup or lookup, with "sum", "if" or "and" statements.

Spent quite a lot of time trying to crack this but couldn't... there are two tables I need help with.

Thank you for any resolutions! And if you could do it in the attachment and upload it so I can see what was done and edit it, that would be even better!

2. ## Re: vLookup the sum of multiple values from multiple criterias

Try

=SUMIFS(Data!\$C\$2:\$C\$15,Data!\$A\$2:\$A\$15,B\$1,Data!\$B\$2:\$B\$15,\$A2)

3. ## Re: vLookup the sum of multiple values from multiple criterias

Sadly this doesn't work. I want to do it assuming the names and activities can appear in the tab data in any order, so I would have to use vlookups to solve this.

Still looking for help and solutions.

4. ## Re: vLookup the sum of multiple values from multiple criterias

Using Vlookup does not solve this problem, see the attachment.

5. ## Re: vLookup the sum of multiple values from multiple criterias

For the second part.
In B10 and copied across.
``Please Login or Register  to view this content.``

6. ## Re: vLookup the sum of multiple values from multiple criterias

THE PROBLEM
"Please see attached. If anyone could solve this, it would be greatly appreciated!

The highlighted cells in Summary tab should show the total training hours spent per person per activity pulled from the Data tab.
i.e. for Adam/Basketball, the cell should show 9 (from 4+5); David/Tennis = 3 (from just 3); Michael/Swimming = 16 (from 4+3+1+8), etc.

For those that don't appear i.e. Sherry/Swimming, it should show a sum of 0 instead of N/A if possible.

Not sure how to use the combination of vLookup or lookup, with "sum", "if" or "and" statements.

Spent quite a lot of time trying to crack this but couldn't... there are two tables I need help with.

Thank you for any resolutions! And if you could do it in the attachment and upload it so I can see what was done and edit it, that would be even better!"

POSSIBLE SOLUTION
In your Table 1, click cell B4 and type this formula: =SUMIFS(Data!\$C\$2:\$C\$15,Data!\$A\$2:\$A\$15,Summary!B\$3,Data!\$B\$2:\$B\$15,Summary!\$A4). Copy it to the rest of the cells. For Activity total sub head in that table, click cell H4 and type this formula: =SUM(B4:G4). copy it to the rest of the cells under that sub head.

In your Table 2, click cell B10 and type this formula: =SUMPRODUCT(ISNUMBER(FIND(Data!\$B\$2:\$B\$15,Summary!B\$9))*(Data!\$A\$2:\$A\$15=Summary!\$A10)*(Data!\$C\$2:\$C\$15)). For Person total sub head in that table, click cell D10 and type this formula: =SUM(B10:C10). copy it to the rest of the cells under that sub head.

The attached excel sheet may help.

7. ## Re: vLookup the sum of multiple values from multiple criterias

Sorry for off-topic interjection:

Jelsay - although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

8. ## Re: vLookup the sum of multiple values from multiple criterias

AliGW,
I hope the detail added to my post above makes it better now. I appreciate your corrections and I want to learn more from more of such, except that, if you may choose to use a milder way of doing that, I will deeply appreciate. Remember to add Reputation to my post.

Looking forward to hearing more.

Thanks.
Jelsay.

9. ## Re: vLookup the sum of multiple values from multiple criterias

1. I am a moderator. The text I used is boilerplate (this means it's a canned message) and is used in instances like this by all moderators. There is nothing wrong with the text as it stands. I do not need to find a 'milder' way of dealing with you: you are no different to any other member here.
2. Why do you think I should give reputation points for your post? Reputation is something you earn and is awarded on a discretionary basis (see the notes at the foot of our forum rules). You do not get reputation just for responding to a moderator's very reasonable and polite request.

You are new here: I suggest you take a moment to read our rules and expectations. Here is a link for your convenience: https://www.excelforum.com/forum-rul...rum-rules.html

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