+ 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
    16.0
    Posts
    25

    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
    2016
    Posts
    5,891

    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
    16.0
    Posts
    25

    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
    2016
    Posts
    5,891

    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, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    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
    6

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,367

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,367

    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)

Similar Threads

  1. [SOLVED] Index-Match-(or Vlookup) for Multiple criterias.
    By Anatawan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2014, 02:38 AM
  2. [SOLVED] Populate Values in Multiple List Boxes based on Multiple Criterias
    By s2jrchoi in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-09-2013, 11:39 AM
  3. [SOLVED] VLOOKUP with multiple criterias
    By GWhaA in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-22-2013, 11:26 PM
  4. Excel 2007 : How to look up values with multiple criterias
    By ralphpammit in forum Excel General
    Replies: 2
    Last Post: 04-03-2012, 11:45 AM
  5. =IF(AND with VLOOKUP with multiple criterias
    By btrescot in forum Excel General
    Replies: 3
    Last Post: 01-24-2011, 07:36 PM
  6. Vlookup Multiple search criterias
    By Rage in forum Excel General
    Replies: 8
    Last Post: 12-01-2010, 12:04 AM
  7. Replies: 2
    Last Post: 07-12-2010, 01:17 PM

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