+ Reply to Thread
Results 1 to 3 of 3

3D VLOOKUP to sum values across multiple sheets?

  1. #1
    Registered User
    Join Date
    04-30-2020
    Location
    Huntsville, AL
    MS-Off Ver
    Office365
    Posts
    2

    3D VLOOKUP to sum values across multiple sheets?

    Hi! I do a sport where you have to keep track of the amount of hours you spend practicing. I want to create a spreadsheet that helps keep track of this information for everyone participating in the sport.

    I'd like to have an overview tab with each person's name and the total number of hours. I'd then like to have an individual sheet for each year. The individual sheets will have the numbers of hours they trained each month totaled and a total year-to-date column that sums that information.

    For instance, say you have John Smith. He's been practicing for all of 2018 and 2019. The YTD column in the 2018 sheet shows 54. The YTD column in the 2019 sheet shows 47. I'd like his total number of hours on the overview page to show as 101.

    However, I can't figure out how to calculate the total number of hours on the overview page. I've tried SUM('2019:2018'!N2). This works well unless the order of names changes (which it may if someone stops practicing). That's why I'd like to use VLookup. I've figured out VLookup for a single sheet. But I don't know how to make VLookup work on a range of sheets like the previous SUM formula does with '2019:2018'.

    I'd also like to avoid just adding multiple vlookup formulas together since that list could get really long as more and more years pass. Which is why I was hoping to find a 3D vlookup formula that solves this issue.

    I hope I explained all of that well enough. I've attached a workbook that contains sample information.

    Any help would be greatly appreciated! Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: 3D VLOOKUP to sum values across multiple sheets?

    Please try at B2
    =SUMPRODUCT(SUMIFS(INDIRECT({"'2018","'2019"}&"'!N:N"),INDIRECT({"'2018","'2019"}&"'!A:A"),A2))

    or Power Query
    Open Power Query/Get and Transform. Click on New Query.
    Open blank query in the editor, launch Advanced Editor and paste in the following code.
    Change file path in blue

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-30-2020
    Location
    Huntsville, AL
    MS-Off Ver
    Office365
    Posts
    2

    Re: 3D VLOOKUP to sum values across multiple sheets?

    Oh! That formula worked!!! You are awesome! Thank you so much!

+ 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. VLOOKUP Count values in multiple sheets
    By Bilbo007 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2019, 05:25 AM
  2. [SOLVED] VLookup - Match- INDEX with multiple values across sheets
    By tylops in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-07-2018, 05:14 PM
  3. [SOLVED] How do i Summarize values from multiple sheets using vlookup combined with...?
    By BillaBoo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2016, 07:43 AM
  4. [SOLVED] SUM VLOOKUP which calculates multiple values across multiple sheets
    By McCaughley7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-04-2016, 03:57 PM
  5. Replies: 2
    Last Post: 10-22-2013, 12:44 PM
  6. Vlookup function to return multiple values from multiple sheets
    By bsamant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2013, 11:31 AM
  7. Vlookup- multiple corresponding values (2 separate sheets)
    By mitelkm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2008, 12:50 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