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

	A	B	C	D	E	F	G	H	I
1	Student	Tutor	23-Jan	24-Jan	25-Jan	26-Jan	29-Jan	30-Jan	31-Jan
2	Josh	Bob T					2:00
3	Nicole	Bob T					2:00
4	Caitie	Amanda M						1:30
5	Hannah	Raymond F
6	Devon	Lexi A	2:00					2:00

<tbody>
</tbody>

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

	A	B	C
1	Period to be summed	29-Jan	31-Jan
2	Tutor	Total Delivered	During this period
3	Amanda M	1:30
4	Bob T	4:00
5	Lexi A	0:00
6	Raymond F	2:00

<tbody>
</tbody>

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!