Avatar of jhorn73
jhorn73 asked on

SUMPRODUCT VLOOKUP Multiple Sheets different structure

I have multiple sheets (new one each week) and a Totals sheet.  On each sheet there is a list of names and a  column with a number.  The Totals sheet has a mast list of names.  Each week new names are added so the current week has more names than the previous week.

I am trying to add up the total on each sheet for each name and present it on the Totals sheet.

Example sheets:
Sheet 1 (week 1)
James    4
Tod        5

Sheet 2 (week 2)
James    2
Lane      7
Tod        1

Totals sheet
Jame      6
Lane      7
Tod        6

I've been working with this site:  https://www.extendoffice.com/documents/excel/2450-excel-vlookup-in-multiple-sheets.html#a1

I've also been playing with this formula:  =SUMPRODUCT(VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$P$110"),A4)>0),0))&"'!$A$1:$P$110"),16,FALSE))

I am using an array formula that uses a sheet that lists all the other sheet names.

Thanks for any help you can provide.
Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

Try a formula like:
=SUMPRODUCT(SUMIF(INDIRECT("'" & INDEX(SheetList,,) & "'!$A2:$A100"),A4,INDIRECT("'" & INDEX(SheetList,,) & "'!$B$2:$B$100")))

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

One nice property of the SUMIF function is that it is aware of the used range of a worksheet, and won't bother searching past the bottom of a worksheet. So you could write the formula using entire columns without losing any unnecessary recalc time:
=SUMPRODUCT(SUMIF(INDIRECT("'" & INDEX(SheetList,,) & "'!$A:$A"),A4,INDIRECT("'" & INDEX(SheetList,,) & "'!$P:$P")))

Note that INDIRECT is a volatile function. Volatile functions recalculate every time any change is made in any open workbook. This isn't a problem if your workbooks are small. But if you are using formulas like this with thousands of rows of data, you may find the recalc delay starts getting annoying.

One workaround for this is to use a user-defined function like SumIf3D:
=SumIf3D(Sheet1:Sheet2!A:A, A4, Sheet1:Sheet2!P:P)

My SumIf3D function uses a true 3D range reference, so it updates if and only if the data changes in those 3D ranges. For this reason, it is computationally much more efficient than simpler appearing equivalents that make the function Volatile to force it to recalculate when required. As mentioned above, volatile functions can cause annoying recalc delays if you use thousands of them.

To incorporate my SumIf3D function in your workbook, you will need to copy all of the code in the Parsing module plus the SumIf3D function from the ThreeDFunctions module.

Thanks for the prompt and excellent solution.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.