Finding the sum of multiple corresponding values in multiple sheets

**sarahw2271** · 2014-08-20, 04:49 AM

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

**NBVC** · 2014-08-20, 02:23 PM

Have a look here to see if you can adapt: http://www.xl-central.com/sum-single...le-sheets.html

**sarahw2271** · 2014-08-20, 04:59 PM

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

**NBVC** · 2014-08-20, 05:14 PM

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.

**sarahw2271** · 2014-08-20, 06:05 PM

here's an example with the last couple weeks

**NBVC** · 2014-08-20, 06:10 PM

Please re-post with samply summary sheet and some expected results....

**sarahw2271** · 2014-08-20, 06:28 PM

here is what it would look like ideally

**NBVC** · 2014-08-20, 07:13 PM

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.

**sarahw2271** · 2014-08-20, 07:41 PM

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

**NBVC** · 2014-08-20, 07:50 PM

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")))