troubleshooting Question

SUMPRODUCT VLOOKUP Multiple Sheets different structure

Avatar of jhorn73
jhorn73 asked on
Microsoft Excel
4 Comments1 Solution357 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros