We help IT Professionals succeed at work.

SUMPRODUCT VLOOKUP Multiple Sheets different structure

jhorn73
jhorn73 asked
on
345 Views
Last Modified: 2016-03-20
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.
Comment
Watch Question

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Try a formula like:
=SUMPRODUCT(SUMIF(INDIRECT("'" & INDEX(SheetList,,) & "'!$A2:$A100"),A4,INDIRECT("'" & INDEX(SheetList,,) & "'!$B$2:$B$100")))
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
3D-functions-8-19-2013.xlsm

Author

Commented:
Thanks for the prompt and excellent solution.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.