Finding the sum of multiple corresponding values in multiple sheets

S

sarahw2271

New member
Joined
Aug 20, 2014
Messages
8
Reaction score
0
Points
0
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 attachedView attachment Example.xlsx
 
help!!! :)

still can figure it out would you give it a shot for me?
 
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.
 
example attached

here's an example with the last couple weeks
 

Attachments

  • example.xlsx
    52.9 KB · Views: 8
Please re-post with samply summary sheet and some expected results....
 
example of results page

here is what it would look like ideally
 

Attachments

  • results page.xlsx
    31.7 KB · Views: 10
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:
still don't get it

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

Attachments

  • Now Ad Breakdown - August 14, 2014.xlsx
    173.7 KB · Views: 11
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")))
 
one more question

this works so well! one other thing I want to tie in is # of tickets sold to each event. i've inserted another sheet with a couple examples of the format we do ticket counts in and added columns for job # and count. how can I adapt that formula to update counts in the summary sheet automatically? (see attached)
 

Attachments

  • Now Ad Breakdown Summary.xlsx
    282.8 KB · Views: 8
Your question is not very clear.

I will assume you want to update column F with count..

Try, in F2:

=IFERROR(INDEX(Count!F:F,MATCH(E2,Count!J:J,0)+1),"")

copied down.

If that's not it, please better explain with sample expected results, etc.
 
almost

trying to get the value from column k of the count sheet to column f of the summary sheet (matching job #'s)

in summary: Job # is column E, count is column F
in count: Job # is column J, count is column K
 
Then try:

=IFERROR(INDEX(Count!K:K,MATCH(E2,Count!J:J,0)),"") This returns blank when job not found

or

=SUMIF(Count!J:J,E2,Count!K:K) This returns 0 when job not found

copied down
 
You must log in or register to reply here.
Back
Top