Two-Way look up with criteria between two dates

[herica]

Below is a mock example of my spreadsheet.

Column B has the names of Tutors who deliver training hours to the Students listed in Column A. And Colums C1:I1 have all the dates for every month (showing is just 23Jan-31Jan).

SS#1
[TABLE="class: outer_border, width: 1352"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student[/TD]
[TD]Tutor[/TD]
[TD]23-Jan[/TD]
[TD]24-Jan[/TD]
[TD]25-Jan[/TD]
[TD]26-Jan[/TD]
[TD]29-Jan[/TD]
[TD]30-Jan[/TD]
[TD]31-Jan[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Josh[/TD]
[TD]Bob T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Nicole[/TD]
[TD]Bob T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Caitie[/TD]
[TD]Amanda M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Hannah[/TD]
[TD]Raymond F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Devon[/TD]
[TD]Lexi A[/TD]
[TD="align: right"]2:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need a formula to give me the underlined answers in the below sample SS (e.g. I need to add up all training hours for each Tutor between 2 dates, and I need these two dates to be able to be changed).

SS#2
[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Period to be summed[/TD]
[TD]29-Jan[/TD]
[TD]31-Jan[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tutor[/TD]
[TD]Total Delivered[/TD]
[TD]During this period[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Amanda M[/TD]
[TD]1:30
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bob T[/TD]
[TD]4:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Lexi A[/TD]
[TD]0:00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Raymond F[/TD]
[TD]2:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The 2 most promising formulas I have are these:


=SUMIFS('SS#1'!C1:I1,'SS#1'!B:B,B2,'SS#1'!C1:I1,">="&'SS#2'!B1,'SS#2'!C1:I1,"<="&C1
which gives me a #VALUE error

And:


=SUMPRODUCT(--('SS#1'!C1:I1>='SS#2'!B1)*('SS#1'!C1:I1<='SS#2'!C1)*('SS#1'!B:B=B2))

This 'works' however gives me a value of 0 so something isn't working there... Does anyone have any ideas?

 

[AhoyNC]

Maybe something like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	1. 1	Student	Tutor	23-Jan	24-Jan	25-Jan	26-Jan	29-Jan	30-Jan	31-Jan
2	2	Josh	Bob T					2:00
3	3	Nicole	Bob T					2:00
4	4	Caitie	Amanda M						1:30
5	5	Hannah	Raymond F
6	6	Devon	Lexi A	2:00					2:00
7	SS#2
8
9		A	B	C
10	1	Period to be summed	29-Jan	31-Jan
11	2	Tutor	Total Delivered	During this period
12	3	Amanda M	1:30
13	4	Bob T	4:00
14	5	Lexi A	2:00
15	6	Raymond F	0:00
Sheet

 

[herica]

Thank you so much! Glad it was actually something 'simple'. Annoying it doesn't work across different spreadsheets/worksheets but hey, at least there's a version that works!