Sum hours by date range by employee ID

[alessiaexcels]

Hi, I am trying to work out an excel formula to sum the hours (yellow shaded column E) for a date range by employee ID. I need this as the bill rate to multiply these hours by changes over time.

I have two sheets:
1) With hours by day - dates across the columns and employee ID/name down the rows.
2) A summary of employee IDs and date ranges for a specific bill rate to apply to that range. I need the total hours by relevant date ranges pulled in here from 1).

I have included both sheets as mini sheets below.

Any help is much appreciated.

Thank you!
Kind regards,
Alessia

Bill example.xlsx
	A	B	C	D	E	F	G
1	ID	Name	Start date	End date (blank if current)	Hours	Bill rate	Bill $
2	100484	Name 1	9/19/23	9/30/23		100	0
3	100484	Name 1	10/1/23	10/26/23		130	0
4	100089	Name 2	9/11/23	9/30/23		50	0
5	100089	Name 2	10/1/23	5/14/24		55	0
6	100089	Name 2	5/15/24			70	0
7	100081	Name 3	9/11/23	9/30/23		145	0
8	100081	Name 3	10/1/23	5/5/24		150	0
9	100081	Name 3	5/6/24			180	0
10	107338	Name 4	11/27/23	6/28/24		125	0
11	100353	Name 5	11/20/23			66	0
12	107450	Name 6	3/1/24			95	0
13	104585	Name 7	4/8/24	5/14/24		110	0
14	104585	Name 7	5/15/24			130	0
15							0
Bill

Cell Formulas
Range	Formula
G2:G14	G2	=E2*F2
G15	G15	=SUM(G2:G14)

Bill example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP	GQ	GR	GS	GT	GU	GV	GW	GX	GY	GZ	HA	HB	HC	HD	HE	HF	HG	HH	HI	HJ	HK	HL	HM	HN	HO	HP	HQ	HR	HS	HT	HU	HV	HW	HX	HY	HZ	IA	IB	IC	ID	IE	IF	IG	IH	II	IJ	IK	IL	IM	IN	IO	IP	IQ	IR	IS	IT	IU	IV	IW	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG	JH	JI	JJ	JK	JL	JM	JN	JO	JP	JQ	JR	JS	JT	JU	JV	JW	JX	JY	JZ	KA	KB	KC	KD	KE	KF	KG	KH	KI
1	ID	Name	9/11/23	9/12/23	9/13/23	9/14/23	9/15/23	9/16/23	9/17/23	9/18/23	9/19/23	9/20/23	9/21/23	9/22/23	9/23/23	9/24/23	9/25/23	9/26/23	9/27/23	9/28/23	9/29/23	9/30/23	10/1/23	10/2/23	10/3/23	10/4/23	10/5/23	10/6/23	10/7/23	10/8/23	10/9/23	10/10/23	10/11/23	10/12/23	10/13/23	10/14/23	10/15/23	10/16/23	10/17/23	10/18/23	10/19/23	10/20/23	10/21/23	10/22/23	10/23/23	10/24/23	10/25/23	10/26/23	10/27/23	10/28/23	10/29/23	10/30/23	10/31/23	11/1/23	11/2/23	11/3/23	11/4/23	11/5/23	11/6/23	11/7/23	11/8/23	11/9/23	11/10/23	11/11/23	11/12/23	11/13/23	11/14/23	11/15/23	11/16/23	11/17/23	11/18/23	11/19/23	11/20/23	11/21/23	11/22/23	11/23/23	11/24/23	11/25/23	11/26/23	11/27/23	11/28/23	11/29/23	11/30/23	12/1/23	12/2/23	12/3/23	12/4/23	12/5/23	12/6/23	12/7/23	12/8/23	12/9/23	12/10/23	12/11/23	12/12/23	12/13/23	12/14/23	12/15/23	12/16/23	12/17/23	12/18/23	12/19/23	12/20/23	12/21/23	12/22/23	12/23/23	12/24/23	12/25/23	12/26/23	12/27/23	12/28/23	12/29/23	12/30/23	12/31/23	1/1/24	1/2/24	1/3/24	1/4/24	1/5/24	1/6/24	1/7/24	1/8/24	1/9/24	1/10/24	1/11/24	1/12/24	1/13/24	1/14/24	1/15/24	1/16/24	1/17/24	1/18/24	1/19/24	1/20/24	1/21/24	1/22/24	1/23/24	1/24/24	1/25/24	1/26/24	1/27/24	1/28/24	1/29/24	1/30/24	1/31/24	2/1/24	2/2/24	2/3/24	2/4/24	2/5/24	2/6/24	2/7/24	2/8/24	2/9/24	2/10/24	2/11/24	2/12/24	2/13/24	2/14/24	2/15/24	2/16/24	2/17/24	2/18/24	2/19/24	2/20/24	2/21/24	2/22/24	2/23/24	2/24/24	2/25/24	2/26/24	2/27/24	2/28/24	2/29/24	3/1/24	3/2/24	3/3/24	3/4/24	3/5/24	3/6/24	3/7/24	3/8/24	3/9/24	3/10/24	3/11/24	3/12/24	3/13/24	3/14/24	3/15/24	3/16/24	3/17/24	3/18/24	3/19/24	3/20/24	3/21/24	3/22/24	3/23/24	3/24/24	3/25/24	3/26/24	3/27/24	3/28/24	3/29/24	3/30/24	3/31/24	4/1/24	4/2/24	4/3/24	4/4/24	4/5/24	4/6/24	4/7/24	4/8/24	4/9/24	4/10/24	4/11/24	4/12/24	4/13/24	4/14/24	4/15/24	4/16/24	4/17/24	4/18/24	4/19/24	4/20/24	4/21/24	4/22/24	4/23/24	4/24/24	4/25/24	4/26/24	4/27/24	4/28/24	4/29/24	4/30/24	5/1/24	5/2/24	5/3/24	5/4/24	5/5/24	5/6/24	5/7/24	5/8/24	5/9/24	5/10/24	5/11/24	5/12/24	5/13/24	5/14/24	5/15/24	5/16/24	5/17/24	5/18/24	5/19/24	5/20/24	5/21/24	5/22/24	5/23/24	5/24/24	5/25/24	5/26/24	5/27/24	5/28/24	5/29/24	5/30/24	5/31/24	6/1/24	6/2/24	6/3/24	6/4/24	6/5/24	6/6/24	6/7/24	6/8/24	6/9/24	6/10/24	6/11/24	6/12/24	6/13/24	6/14/24	6/15/24	6/16/24	6/17/24	6/18/24	6/19/24	6/20/24	6/21/24	6/22/24	6/23/24	6/24/24	6/25/24	6/26/24	6/27/24	6/28/24	TOTAL
2	100484	Name 1									8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8																																																																																																																																																																																																																																																							48
3	100089	Name 2	8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8																						8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8																		8	8	8	8	8	8								8	8	8	8	8	8										8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8														8	8	8	8	8	8												10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10					10	10	10	10	10	10							10	10	10	10	10	10										10	10	10	10	10	10								10	10	10	10	10	10	1188
4	100081	Name 3	8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8																			10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10																	10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10																										10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10											10	10	10	10	10	10																							10	10	10	10	10	10		10	10	10	10	10	10																							10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10											10	10	10	10	10	10		10	10	10	10	10	10							1200
5	107338	Name 4																																																																														8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	8		8	8	8	8	8	1480
6	100353	Name 5																																																																							8	8	8	8	8	8			8	8	8	8	8	8		8	8	8	8	8	8			8	8	8	8	8	8		8	8	8	8	8	8			8	8	8	8	8	8		8	8	8	8	8	8			8	8	8	8	8	8								10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10											10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10														10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10															10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10																			10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		1284
7	107450	Name 6																																																																																																																																																																													8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8		8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8		8	8	8	8	8	8			8	8	8	8	8	8			8	8	8	8	8	8			8	8	736
8	104585	Name 7																																																																																																																																																																																																																																																								10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10		10	10	10	10	10	10					360
Hours to 28JUN24

Cell Formulas
Range	Formula
KI2:KI8	KI2	=SUM(AQ2:KH2)

 

[RoryA]

You should be able to use something like this:

=SUMIFS(INDEX('Hours to 28JUN24'!$C$2:$KH$8,MATCH(A2,'Hours to 28JUN24'!$A$2:$A$8,0),0),'Hours to 28JUN24'!$C$1:$KH$1,">="&$C2,'Hours to 28JUN24'!$C$1:$KH$1,"<="&$D2)

adjusting the ranges to cover the real data rows

 

[Kaper]

Another option:

=sumproduct('Hours to 28JUN24'!$C$2:$KH$8,('Hours to 28JUN24'!$B$2:$B$8=B2)*('Hours to 28JUN24'!$C$1:$KH$1>=C2)*('Hours to 28JUN24'!$C$1:$KH$1>=if(D2="",TODAY(),D2)))

You may use the last part also in Rory's formula:

... ,'Hours to 28JUN24'!$C$1:$KH$1,"<="&if(D2="",TODAY(),D2))