SumIf, Index, Match Need help with formula

[IESEnergy]

I would like to calculate the total hours for M, T, W, Th, F, Sa, Su

Can you please help with the formula? I was able to get the total hours for each employee but haven't been able to figure out how to break it down per day.

I have the following table:

Employee Name	Employee Number	8/13/2018	8/14/2018	8/15/2018	8/16/2018	8/17/2018	8/18/2018	8/19/2018	Total Hours
		M	T	W	Th	F	Sa	Su
Name1	35EB600006
Name2	35EB600092

<tbody>
</tbody>

And the following data on sheet1:

Employee Name	Employee #	Date	InTime	OutTime	Hours
Name1	35EB600006	8/13/2018	8:00 AM	12:00 PM	4
Name1	35EB600006	8/13/2018	12:30 PM	4:03 PM	3.55
Name1	35EB600006	8/14/2018	6:30 AM	4:08 PM	9.633
Name1	35EB600006	8/15/2018	11:40 AM	4:56 PM	5.267
Name1	35EB600006	8/16/2018	8:00 AM	4:00 PM	8
Name1	35EB600006	8/17/2018	7:35 AM	4:00 PM	8.417
Name1	35EB600006	8/18/2018	11:50 AM	2:10 PM	2.333
Name2	35EB600092	8/13/2018	8:01 AM	12:01 PM	4
Name2	35EB600092	8/13/2018	12:33 PM	4:05 PM	3.533
Name2	35EB600092	8/14/2018	7:55 AM	12:01 PM	4.1
Name2	35EB600092	8/14/2018	12:36 PM	4:14 PM	3.633
Name2	35EB600092	8/15/2018	8:04 AM	12:00 PM	3.933
Name2	35EB600092	8/15/2018	12:37 PM	4:01 PM	3.4
Name2	35EB600092	8/16/2018	8:00 AM	4:00 PM	8
Name2	35EB600092	8/17/2018	8:30 AM	4:00 PM	7.5

<tbody>
</tbody>

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Employee Name​	Employee Number​	8/13/2018​	8/14/2018​	8/15/2018​	8/16/2018​	8/17/2018​	8/18/2018​	8/19/2018​	Total Hours​
2​			M​	T​	W​	Th​	F​	Sa​	Su​
3​	Name1	35EB600006	7.55​	9.633​	5.267​	8​	8.417​	2.333​	0​	41.2​
4​	Name2	35EB600092	7.533​	7.733​	7.333​	8​	7.5​	0​	0​	38.099​
5​
6​
7​
8​
9​
10​	Employee Name​	Employee #​	Date​	InTime​	OutTime​	Hours​
11​	Name1	35EB600006	8/13/2018​	8:00 AM​	12:00 PM​	4​
12​	Name1	35EB600006	8/13/2018​	12:30 PM​	4:03 PM​	3.55​
13​	Name1	35EB600006	8/14/2018​	6:30 AM​	4:08 PM​	9.633​
14​	Name1	35EB600006	8/15/2018​	11:40 AM​	4:56 PM​	5.267​
15​	Name1	35EB600006	8/16/2018​	8:00 AM​	4:00 PM​	8​
16​	Name1	35EB600006	8/17/2018​	7:35 AM​	4:00 PM​	8.417​
17​	Name1	35EB600006	8/18/2018​	11:50 AM​	2:10 PM​	2.333​
18​	Name2	35EB600092	8/13/2018​	8:01 AM​	12:01 PM​	4​
19​	Name2	35EB600092	8/13/2018​	12:33 PM​	4:05 PM​	3.533​
20​	Name2	35EB600092	8/14/2018​	7:55 AM​	12:01 PM​	4.1​
21​	Name2	35EB600092	8/14/2018​	12:36 PM​	4:14 PM​	3.633​
22​	Name2	35EB600092	8/15/2018​	8:04 AM​	12:00 PM​	3.933​
23​	Name2	35EB600092	8/15/2018​	12:37 PM​	4:01 PM​	3.4​
24​	Name2	35EB600092	8/16/2018​	8:00 AM​	4:00 PM​	8​
25​	Name2	35EB600092	8/17/2018​	8:30 AM​	4:00 PM​	7.5​

<tbody>
</tbody>

C3=SUMIFS($F$11:$F$25,$B$11:$B$25,$B3,$C$11:$C$25,C$1) copy across and down

j3=SUM(C3:I3) copy down

 

[IESEnergy]

Thank you very much. Is there a way to do it by a look up? Some of the employees did not work Monday. Some did not take a lunch, etc.

 

[MARZIOTULLIO]

Hi

Can you provide some sample data and what the expected outcome is?

 

[IESEnergy]

https://www.dropbox.com/s/rp8sqxp947uzhpw/Book1.xlsx?dl=0

Here is the template. Thank you so much for your help.

 

[MARZIOTULLIO]

Hi

Sheet2

C3=SUMIFS(Sheet1!$F$2:$F$93,Sheet1!$B$2:$B$93,Sheet2!$B3,Sheet1!$A$2:$A$93,Sheet2!$A3,Sheet1!$C$2:$C$93,Sheet2!C$1) copy across and down

 

[IESEnergy]

Thank you so much! It worked perfectly.