Possible Sum/Index/Lookup Formula?

[ekramp]

I have a spreadsheet showing how many hours each employee works per week. I’m trying to get total hours work for a specific date range for specific employees.
I want to be able to:
Input a date into N2 (which would match a date in column A)
Input a date into N3 (which would match a date in column A)
Select an Employee name from a dropdown in N4 (data validation)
Get a total hours for that date range in N5

The total hours for Employee G for date range 22-Jan thru 26-Mar (currently yellow) should be 191.00 hours not the 338.5 it’s calculating. I know it has to incorporate an Employee Lookup somewhere in the formula but I can’t figure that part out. Maybe a Sum/Index/Lookup/Match isn’t the best formula to use either.

hours.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Hours Summary												Start Date	22-Jan
3													End Date	26-Mar
4	Employee:	A	B	C	D	E	F	G	H	I			Employee	G
5													Total Hours	338.5
6	1-Jan	45.25	44.00	40.00	41.00	40.00	46.00	45.00	40.00	36.25
7	8-Jan	46.75	43.50	40.25	40.00	40.00	46.25	51.50	40.00	39.25
8	15-Jan	62.00	44.50	41.00	40.00	40.50	46.50	51.00	41.00	40.00
9	22-Jan	62.00	43.00	40.50	40.00	41.25	46.50	48.00	40.50	40.25
10	29-Jan	40.00	44.00	41.50	43.00	43.00	46.25	50.00	39.50	31.25
11	5-Feb	45.00	43.00	39.00	40.00	40.75	46.25	44.50	43.50	39.25
12	12-Feb	41.50	44.50	40.75	45.00	42.50	47.75	48.50	41.25	42.00
13	19-Feb	-	-	-	-	-	-	-	-	-
14	26-Feb	-	-	-	-	-	-	-	-	-
15	5-Mar	-	-	-	-	-	-	-	-	-
16	12-Mar	-	-	-	-	-	-	-	-	-
17	19-Mar	-	-	-	-	-	-	-	-	-
18	26-Mar	-	-	-	-	-	-	-	-	-
19	2-Apr	-	-	-	-	-	-	-	-	-
20	9-Apr	-	-	-	-	-	-	-	-	-
21	16-Apr	-	-	-	-	-	-	-	-	-
22	23-Apr	-	-	-	-	-	-	-	-	-
23	30-Apr	-	-	-	-	-	-	-	-	-
24	7-May	-	-	-	-	-	-	-	-	-
25	14-May	-	-	-	-	-	-	-	-	-
26	21-May	-	-	-	-	-	-	-	-	-
27	28-May	-	-	-	-	-	-	-	-	-
28	4-Jun	-	-	-	-	-	-	-	-	-
29	11-Jun	-	-	-	-	-	-	-	-	-
30	18-Jun	-	-	-	-	-	-	-	-	-
31	25-Jun	-	-	-	-	-	-	-	-	-
Sheet1

Cell Formulas
Range	Formula
N5	N5	=SUM(INDEX(B6:J57,0,MATCH(N4,Employee,0)))

Named Ranges
Name	Refers To	Cells
Employee	=Sheet1!$B$4:$J$4	N5

Cells with Data Validation
Cell	Allow	Criteria
N4	List	=Employee

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUM(FILTER(FILTER(B6:J31,(A6:A31>=N2)*(A6:A31<=N3)),N4=Employee))

 

[Snakehips]

Could use.

=SUMIFS(INDEX(B6:J57,0,MATCH(N4,B4:J4,0)),A6:A57,">="&N2,A6:A57,"<="&N3)

 

[ken2step]

I only have Excel2010 so I don't know what the Filter function does......but SUMPRODUCT will also work. Using your example above...

=SUMPRODUCT(--(Date>=N2),--(Date<=N3),G)

That formula uses named ranges which correlate with your column headings and range below them.

 

[ekramp]

Hi & welcome to MrExcel.
How about

=SUM(FILTER(FILTER(B6:J31,(A6:A31>=N2)*(A6:A31<=N3)),N4=Employee))

This formula worked! I never thought about trying <>= portion. Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.