Lookup based on multiple criteria and date Range

[hpatel7]

Hello All,

I'm trying to do a lookup wherein "Log" pulls the appropriate hours from column C of "TimeSheet" based on the name and the date. I'd like for the hours to populate the appropriate month in columns J-U in "Log" based on the number they entered in the TimeSheet. Expected results are populated in columns J-U. The tricky part is that there are 2 people who are in there twice under different work orders under different start/end dates.

Any advice here would be helpful!

Thank you!

Log

IT Retainer Request_sample2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	#	Date Submitted	Role	Start Date	End Date	Estimated Hours per Month (per resource)	Work Order Number	Resource Name	Budgeted Bill Rate	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22
2	16	10/18/2021 2:25 PM	PMO Advisor	9/7/2021	3/31/2022	40	3	Laura	$162.00	0	0	0	0	0	0	0	0	0	0	0	0
3	15	10/18/2021 2:25 PM	Project Support	9/7/2021	3/31/2022	40	3	Larry	$135.00	0	0	39.5	0	0	0	0	0	0	0	0	0
4	13	10/18/2021 1:36 PM	Optime ID/PT	7/26/2021	1/28/2022	173	2	Lekan	$121.50	40	160	200	0	0	0	0	0	0	0	0	0
5	12	10/18/2021 1:28 PM	Project Support	7/12/2021	8/27/2021	15	1	Laura	$135.00	0	20	37.25	0	0	0	0	0	0	0	0	0
6	11	10/18/2021 1:28 PM	PMO Advisor	7/12/2021	8/27/2021	40	1	Larry	$162.00	18.5	40.25	0	0	0	0	0	0	0	0	0	0
Log

TimeSheet

IT Retainer Request_sample2.xlsx
	A	B	C
1	Date	Employee	Hours
2	7/12/2021	Larry	1
3	7/14/2021	Larry	2
4	7/16/2021	Larry	2
5	7/19/2021	Larry	2.5
6	7/20/2021	Larry	1
7	7/21/2021	Larry	1
8	7/22/2021	Larry	1.5
9	7/23/2021	Larry	2
10	7/26/2021	Larry	1.5
11	7/26/2021	Lekan	10
12	7/27/2021	Larry	4
13	7/27/2021	Lekan	10
14	7/28/2021	Lekan	10
15	7/29/2021	Lekan	10
16	8/1/2021	Larry	3
17	8/2/2021	Larry	4
18	8/2/2021	Lekan	10
19	8/2/2021	Laura	1.75
20	8/3/2021	Larry	2.5
21	8/3/2021	Lekan	10
22	8/3/2021	Laura	3.75
23	8/4/2021	Larry	5.5
24	8/4/2021	Lekan	10
25	8/4/2021	Laura	2
26	8/5/2021	Larry	4.5
27	8/5/2021	Lekan	10
28	8/5/2021	Laura	4.5
29	8/6/2021	Larry	2
30	8/6/2021	Laura	1
31	8/8/2021	Larry	0.5
32	8/9/2021	Larry	1.5
33	8/9/2021	Lekan	10
34	8/9/2021	Laura	1
35	8/10/2021	Lekan	10
36	8/11/2021	Larry	2.5
37	8/11/2021	Lekan	10
38	8/11/2021	Laura	0.5
39	8/12/2021	Larry	1
40	8/12/2021	Lekan	10
41	8/13/2021	Larry	0.5
42	8/15/2021	Larry	1.25
43	8/16/2021	Larry	0.5
44	8/16/2021	Lekan	10
45	8/17/2021	Larry	3
46	8/17/2021	Lekan	10
47	8/17/2021	Laura	2.75
48	8/18/2021	Larry	1
49	8/18/2021	Lekan	10
50	8/18/2021	Laura	2
51	8/19/2021	Larry	1.5
52	8/19/2021	Lekan	10
53	8/19/2021	Laura	0.5
54	8/23/2021	Larry	0.5
55	8/23/2021	Lekan	10
56	8/24/2021	Lekan	10
57	8/25/2021	Larry	1.5
58	8/25/2021	Lekan	10
59	8/25/2021	Laura	0.25
60	8/26/2021	Larry	2
61	8/26/2021	Lekan	10
62	8/27/2021	Larry	0.5
63	8/30/2021	Larry	1
64	9/1/2021	Larry	0.5
65	9/1/2021	Lekan	10
66	9/2/2021	Lekan	10
67	9/3/2021	Lekan	10
68	9/4/2021	Lekan	10
69	9/7/2021	Lekan	10
70	9/8/2021	Larry	0.5
71	9/8/2021	Lekan	10
72	9/9/2021	Larry	3
73	9/9/2021	Lekan	10
74	9/9/2021	Laura	2.5
75	9/10/2021	Larry	2.5
76	9/10/2021	Lekan	10
77	9/10/2021	Laura	1.5
78	9/13/2021	Larry	2
79	9/13/2021	Lekan	10
80	9/13/2021	Laura	1
81	9/14/2021	Larry	5
82	9/14/2021	Lekan	10
83	9/14/2021	Laura	4
84	9/15/2021	Larry	5
85	9/15/2021	Lekan	10
86	9/15/2021	Laura	4.25
87	9/16/2021	Lekan	10
88	9/17/2021	Larry	3
89	9/18/2021	Larry	0.5
90	9/20/2021	Larry	3
91	9/20/2021	Lekan	10
92	9/20/2021	Laura	1.75
93	9/21/2021	Larry	1
94	9/21/2021	Lekan	10
95	9/21/2021	Laura	1.5
96	9/22/2021	Larry	1
97	9/22/2021	Lekan	10
98	9/22/2021	Laura	3
99	9/23/2021	Larry	4
100	9/23/2021	Lekan	10
101	9/23/2021	Laura	4
102	9/24/2021	Larry	1
103	9/24/2021	Laura	0.5
104	9/27/2021	Lekan	10
105	9/27/2021	Laura	2.5
106	9/28/2021	Larry	2.5
107	9/28/2021	Lekan	10
108	9/28/2021	Laura	5.75
109	9/29/2021	Larry	4
110	9/29/2021	Lekan	10
111	9/29/2021	Laura	3
112	9/30/2021	Larry	1
113	9/30/2021	Lekan	10
114	9/30/2021	Laura	2
TimeSheet

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(AND(J$1<$E2,EOMONTH(J$1,0)>$D2),SUMIFS(TimeSheet!$C:$C,TimeSheet!$B:$B,$H2,TimeSheet!$A:$A,">="&J$1,TimeSheet!$A:$A,"<="&EOMONTH(J$1,0)),0)

 

[Toadstool]

Hi HPatel7,

SUMIFS will do this but you may want to do a cross-check.
e.g. the total hours on TimeSheet are 555.5 but the total hours reported on Log are 554. The discrepancy is Larry with 1 hour on 30 August 2021 and 0.5 hours on 1 September 2021, but the two date ranges for Larry (07-Sep-21 to 31-Mar-22, 12-Jul-21 to 27-Aug-21) do not include those dates.

Please also note that as Timesheet does not include a Work Order Number then if the same name has multiple overlapping Start/End Dates then hours in the overlap period will be double counted.

HPatel7.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	#	Date Submitted	Role	Start Date	End Date	Estimated Hours per Month (per resource)	Work Order Number	Resource Name	Budgeted Bill Rate	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22
2	16	44487.6	PMO Advisor	07-Sep-21	31-Mar-22	40	3	Laura	162	0	0	37.25	0	0	0	0	0	0	0	0	0
3	15	44487.6	Project Support	07-Sep-21	31-Mar-22	40	3	Larry	135	0	0	39	0	0	0	0	0	0	0	0	0
4	13	44487.57	Optime ID/PT	26-Jul-21	28-Jan-22	173	2	Lekan	121.5	40	160	200	0	0	0	0	0	0	0	0	0
5	12	44487.56	Project Support	12-Jul-21	27-Aug-21	15	1	Laura	135	0	20	0	0	0	0	0	0	0	0	0	0
6	11	44487.56	PMO Advisor	12-Jul-21	27-Aug-21	40	1	Larry	162	18.5	39.25	0	0	0	0	0	0	0	0	0	0
Log

Cell Formulas
Range	Formula
K1:U1	K1	=EOMONTH(J1,0)+1
J2:U6	J2	=SUMIFS(TimeSheet!$C:$C,TimeSheet!$B:$B,$H2,TimeSheet!$A:$A,">="&J$1,TimeSheet!$A:$A,"<="&EOMONTH(J$1,0),TimeSheet!$A:$A,">="&$D2,TimeSheet!$A:$A,"<="&$E2)

Named Ranges
Name	Refers To	Cells
TimeSheet!_FilterDatabase	=TimeSheet!$A$1:$F$114	J2:U6