+ Reply to Thread
Results 1 to 9 of 9

vLookup the sum of multiple values from multiple criterias

  1. #1
    Registered User
    Join Date
    07-21-2017
    Location
    NY
    MS-Off Ver
    15.33
    Posts
    12

    Unhappy 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!
    Attached Files Attached Files
    Last edited by jos283; 04-15-2019 at 08:03 PM.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    3,502

    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. #3
    Registered User
    Join Date
    07-21-2017
    Location
    NY
    MS-Off Ver
    15.33
    Posts
    12

    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.
    Last edited by jos283; 04-15-2019 at 08:04 PM.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    3,502

    Re: vLookup the sum of multiple values from multiple criterias

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

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,379

    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.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    11-30-2016
    Location
    Mararaba,Nigeria
    MS-Off Ver
    2013
    Posts
    3

    Smile 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.
    Attached Files Attached Files
    Last edited by Jelsay; 04-17-2019 at 04:42 AM. Reason: To add details

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    24,763

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  8. #8
    Registered User
    Join Date
    11-30-2016
    Location
    Mararaba,Nigeria
    MS-Off Ver
    2013
    Posts
    3

    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. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    24,763

    Re: vLookup the sum of multiple values from multiple criterias

    Jelsay - thank you for the added detail. Please note:

    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
    Last edited by AliGW; 04-17-2019 at 05:23 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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