Link to home
Start Free TrialLog in
Avatar of 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:

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.
Avatar of byundt
Flag of United States of America image

Try a formula like:
=SUMPRODUCT(SUMIF(INDIRECT("'" & INDEX(SheetList,,) & "'!$A2:$A100"),A4,INDIRECT("'" & INDEX(SheetList,,) & "'!$B$2:$B$100")))
Avatar of byundt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of jhorn73


Thanks for the prompt and excellent solution.