# Index & match SUM multiple worksheets

1. ## Index & match SUM multiple worksheets

Hi there,

I am struggling with this one.

I am trying to update a summary worksheet which lists all staff members and the total expenses associated to them across multiple worksheets

Is there are way I can do index & match against their name across multple worksheets to insert the sum of all values across all sheets?

If not, is there an easy way to vlookup for this as I really don't want to do it manually

2. ## Re: Index & match SUM multiple worksheets

Hi

have you tried Data consolidation ?

3. ## Re: Index & match SUM multiple worksheets

Put this in B2 of your summary sheet:

=SUMIF(INDIRECT("'Ent-"&TEXT(B\$1,"mmmyy")&"'!A:A"),\$A2,INDIRECT("'Ent-"&TEXT(B\$1,"mmmyy")&"'!B:B"))

Then you can copy it across and down to get the data for each month and for each name.

Hope this helps.

Pete

4. ## Re: Index & match SUM multiple worksheets

Please mark this as solved if you have received a satifactory solution.

5. ## Re: Index & match SUM multiple worksheets

Thanks so much for that Pete!

I've inserted the formula as you've instructed and its worked for the EntApr11-EntFeb12 worksheets but I'm missing the last 5 worksheets.

I basically need a total for each staff member across all worksheets

I have now renamed so that it begins with "Ent-"TEXT but it doesn't seem to be adding the rest

Is there another way I can add on to that formula to have the last 5 worksheets added as well?

Thanks,

Thi

6. ## Re: Index & match SUM multiple worksheets

To pick up those other 5 sheets, you would need to modify the formula to this in B2:

=SUMIF(INDIRECT("'Ent-"&TEXT(B\$1,"mmmyy")&"'!A:A"),\$A2,INDIRECT("'Ent-"&TEXT(B\$1,"mmmyy")&"'!B:B"))+IFERROR(INDEX('Ent-RHR'!\$B:\$M,MATCH(\$A2,'Ent-RHR'!\$A:\$A,0),MATCH(B\$1,'Ent-RHR'!B\$1:M\$1)),0)+IFERROR(INDEX('Ent-Other'!\$B:\$M,MATCH(\$A2,'Ent-Other'!\$A:\$A,0),MATCH(B\$1,'Ent-Other'!B\$1:M\$1)),0)+IF(B\$1='Ent-Fraser'!\$B\$1,SUMIF('Ent-Fraser'!\$A:\$A,\$A2,'Ent-Fraser'!\$B:\$B),0)+IF(B\$1='Ent-10Yr'!\$B\$1,SUMIF('Ent-10Yr'!\$A:\$A,\$A2,'Ent-10Yr'!\$B:\$B),0)+IF(B\$1='Ent-Xmas'!\$B\$1,SUMIF('Ent-Xmas'!\$A:\$A,\$A2,'Ent-Xmas'!\$B:\$B),0)

I've done this for you in the attached workbook.

Hope this helps.

Pete

PS. If this solves your problem please mark the Thread as Solved (the FAQ tells you how). Also, if there is any particular thread that has helped you, then you can click on the "star" icon in the bottom left corner to pass on your thanks directly.

7. ## Re: Index & match SUM multiple worksheets

Thanks a mil Pete!!!

8. ## Re: Index & match SUM multiple worksheets

Thito: Thanks for asking the this list how to do this.

Pete_UK: Thank you very much for providing such an excellent example of how to index / match across multiple worksheets. I've been trying to figure this out for a long time!

Kind regards,

-dave myers

9. ## Re: Index & match SUM multiple worksheets

You're welcome, Dave - thanks for taking the trouble to post that.

Pete

10. ## Re: Index & match SUM multiple worksheets

Dave – I highly recommend rearranging the way you set up the information in this workbook.

I'd take all the numbers on the RHR, Other, Fraser, 10Yr, Xmas sheets and list them on the individual monthly sheets (see the Apr worksheet in the attached for an example).

With all due respect to Pete (he’s obviously an Excel Power User), that is the most ridiculous thing I’ve ever seen. Good luck trying to amend that formula down the road, unnecessarily complicated.

Also, I’d remove the “Ent” from the worksheet names and make life a little easier.

Then you can do a more classic Index/Indirect/Match inside an If/Iserror function (see attached), which is what I think you were initially inquiring about, Dave. I think this is even pushing the envelope, having such a long formula, prone to model error, not a modeling best practice.

Thanks for the exercise.

11. ## Re: Index & match SUM multiple worksheets

@ediaz:

you say that is the most ridiculous thing you have ever seen because you have not hung around this (i am assuming) forum for very long.

also, i think a little bit of civility is in order, even if you tend to disagree with others' approach. everyone has an opportunity to provide a solution or enhance on other solutions, albeit with an amiable tone and tenor.

that said, i liked your solution (better).

12. ## Re: Index & match SUM multiple worksheets

My apologies to the group, for coming across a little rude.
It was kind of late when I was responding and still had my little one up and running around.
I appreciate the opportunity to practice on here, and thank you for indulging me.
Kindest regards,
Erick

13. ## Re: Index & match SUM multiple worksheets

I think that you could use iferror instead of an if statement and iserror.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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