+ Reply to Thread
Results 1 to 13 of 13

Index & match SUM multiple worksheets

  1. #1
    Registered User
    Join Date
    09-27-2010
    Location
    Sydney Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question 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

    Please help!
    Attached Files Attached Files
    Last edited by Thito; 03-21-2012 at 05:17 PM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Index & match SUM multiple worksheets

    Hi

    have you tried Data consolidation ?

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #4
    Forum Contributor darknation144's Avatar
    Join Date
    01-24-2012
    Location
    London
    MS-Off Ver
    Microsoft Excel 365 MSO
    Posts
    555

    Re: Index & match SUM multiple worksheets

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

  5. #5
    Registered User
    Join Date
    09-27-2010
    Location
    Sydney Australia
    MS-Off Ver
    Excel 2007
    Posts
    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
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-27-2010
    Location
    Sydney Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Red face Re: Index & match SUM multiple worksheets

    Thanks a mil Pete!!!

    You have been so helpful!!!!

  8. #8
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    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. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Index & match SUM multiple worksheets

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

    Pete

  10. #10
    Registered User
    Join Date
    05-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    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.
    Attached Files Attached Files

  11. #11
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    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).
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  12. #12
    Registered User
    Join Date
    05-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #13
    Registered User
    Join Date
    04-02-2014
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Index & match SUM multiple worksheets

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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