Summary for Date & Instructor Across several sheets in one workbook

[RAF43]

Hello everyone,

Below is the worksheet I'm wanting to summarize information from.

I'm wanting to summarize HOURS for each DATE and for each INSTRUCTOR across all (33) worksheets, named Week 1 to Week 33.




Below is the summary sheet I have created.

I'm wanting to extract info from the above worksheet for each instructor to total for HOURS on each DATE for a specific INSTRUCTOR.

I've used the indirect() function elsewhere in the spreadsheet with one criteria, however I can't seem to make that work with two criteria (date & instructor).

Any help would be appreciated -
Many thanks,
Randy

[/URL][/IMG]

 

[RAF43]

Bump ...

It seems I should be able to modify the sumif() to SUMIFS(), and add a DATE criteria, but I can't get it to work.
Current formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&$J$3:$J$35&"'!$C$3:$C$36"),$C9,INDIRECT("'"&$J$3:$J$35&"'!$F$3:$F$36")))
In the above formula: J3:J35 is list of worksheet names, C3:C36 is instructor column, C9 is specific instructor, F3:F36 is hours column.

Any suggestions please ...

Thx,
Randy

 

[RAF43]

Hello All -

Finally got were I wanted to go using the below formulas. It took a unique group of five to make the week, then I was able to drag across. After that I copied the group of 5 and cut and pasted down, using the word replace function to correct for the Week 1, Week 2 etc tab names .... It was a VERY clumsy process. I could not get the INDIRECT() function to calculate correctly like I wanted. I ended up doing a lot of cut and past work. Like I said, not elegant, but it works.

I was hoping somebody might want to improve on the approach ......

=IF('Week 1'!$A$6=$C3,SUMPRODUCT(SUMIF('Week 1'!$C$3:$C$8,D$2,'Week 1'!$F$3:$F$8)+SUMIF('Week 1'!$H$3:$H$8,D$2,'Week 1'!$K$3:$K$8)+SUMIF('Week 1'!$M$3:$M$8,D$2,'Week 1'!$P$3:$P$8)),"")

=IF('Week 1'!$A$13=$C4,SUMPRODUCT(SUMIF('Week 1'!$C$10:$C$15,D$2,'Week 1'!$F$10:$F$15)+SUMIF('Week 1'!$H$10:$H$15,D$2,'Week 1'!$K$10:$K$15)+SUMIF('Week 1'!$M$10:$M$15,D$2,'Week 1'!$P$10:$P$15))

=IF('Week 1'!$A$20=$C5,SUMPRODUCT(SUMIF('Week 1'!$C$17:$C$22,D$2,'Week 1'!$F$17:$F$22)+SUMIF('Week 1'!$H$17:$H$22,D$2,'Week 1'!$K$17:$K$22)+SUMIF('Week 1'!$M$17:$M$22,D$2,'Week 1'!$P$17:$P$22))

=IF('Week 1'!$A$27=$C6,SUMPRODUCT(SUMIF('Week 1'!$C$24:$C$29,D$2,'Week 1'!$F$24:$F$29)+SUMIF('Week 1'!$H$24:$H$29,D$2,'Week 1'!$K$24:$K$29)+SUMIF('Week 1'!$M$24:$M$29,D$2,'Week 1'!$P$24:$P$29))

=IF('Week 1'!$A$34=$C7,SUMPRODUCT(SUMIF('Week 1'!$C$31:$C$36,D$2,'Week 1'!$F$31:$F$36)+SUMIF('Week 1'!$H$31:$H$36,D$2,'Week 1'!$K$31:$K$36)+SUMIF('Week 1'!$M$31:$M$36,D$2,'Week 1'!$P$31:$P$36))

Thank you,
Randy