Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Finding the sum of multiple corresponding values in multiple sheets

  1. #1

    Finding the sum of multiple corresponding values in multiple sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a workbook with tons of sheets set up. Each week I do a report where I breakdown the amount we spent advertising our various events in a one page ad in a local alt weekly. So each week has it's own sheet within the workbook. Each event is assigned a job number (2 letters and 4 digits), which is entered in column A. The spend for each event is calculated in column I. I want to find out how to create a formula for determining how much I have spent on any given event to date (i.e. a separate sheet with a total for each job number that updates automatically every week when I do the breakdown. Please let me know if you have any idea how to do this. Example attachedExample.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Have a look here to see if you can adapt: http://www.xl-central.com/sum-single...le-sheets.html


  3. #3

    help!!! :)

    still can figure it out would you give it a shot for me?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Post a sample workbook (doesn't need all the sheet or all the info, just relevant/edited info). Make sure things like location of data, summary page layout, and sheetnames are all relevant to your actuals.


  5. #5

    example attached

    here's an example with the last couple weeks
    Attached Files Attached Files

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Please re-post with samply summary sheet and some expected results....


  7. #7

    example of results page

    here is what it would look like ideally
    Attached Files Attached Files

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Ok, I interpreted your original question differently than you indicate with your summary sheet.

    Assuming your summary sheet is in the same workbook as the weekly results, then in B2 of the Summary sheet enter formula:

    =IF($A2="","",SUMIF(INDIRECT("'"&TEXT(C$1,"mmm d, yyyy")&"'!A:A"),$A2,INDIRECT("'"&TEXT(C$1,"mmm d, yyyy")&"'!I:I")))

    copied across and down as far as necessary.

    Furthermore, if your jobs are lists are the same in all sheets, and is listed identically in the summary sheet, then you can just point the respective cell in column I... with:

    =IF($A2="","",INDIRECT("'"&TEXT(C$1,"mmm d, yyyy")&"'!I"&ROWS($A$1:$A6)))

    copied across and down as far as necessary.
    Last edited by NBVC; 2014-08-20 at 06:16 PM.


  9. #9

    still don't get it

    can you try on the actual sheet and see if it works for you?
    Attached Files Attached Files

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    The sheetnames in your sample weren't the same format as this. You need another "m" in the TEXT function:

    =IF($A2="","",SUMIF(INDIRECT("'"&TEXT(C$1,"mmmm d, yyyy")&"'!A:A"),$A2,INDIRECT("'"&TEXT(C$1,"mmmm d, yyyy")&"'!I:I")))


Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •