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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Employee Name[/TD]
[TD="align: center"]Employee Number[/TD]
[TD="align: center"]8/13/2018[/TD]
[TD="align: center"]8/14/2018[/TD]
[TD="align: center"]8/15/2018[/TD]
[TD="align: center"]8/16/2018[/TD]
[TD="align: center"]8/17/2018[/TD]
[TD="align: center"]8/18/2018[/TD]
[TD="align: center"]8/19/2018[/TD]
[TD="align: center"]Total Hours[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]Th[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]Sa[/TD]
[TD="align: center"]Su[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]



And the following data on sheet1:

[TABLE="width: 739"]
<tbody>[TR]
[TD="align: center"]Employee Name[/TD]
[TD="align: center"] Employee #[/TD]
[TD="align: center"] Date[/TD]
[TD="align: center"] InTime[/TD]
[TD="align: center"] OutTime[/TD]
[TD="align: center"] Hours[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD="align: right"]8/13/2018[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD="align: right"]8/13/2018[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]4:03 PM[/TD]
[TD="align: right"]3.55[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD="align: right"]8/14/2018[/TD]
[TD="align: right"]6:30 AM[/TD]
[TD="align: right"]4:08 PM[/TD]
[TD="align: right"]9.633[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD="align: right"]8/15/2018[/TD]
[TD="align: right"]11:40 AM[/TD]
[TD="align: right"]4:56 PM[/TD]
[TD="align: right"]5.267[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD="align: right"]8/16/2018[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD="align: right"]8/17/2018[/TD]
[TD="align: right"]7:35 AM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]8.417[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD="align: right"]8/18/2018[/TD]
[TD="align: right"]11:50 AM[/TD]
[TD="align: right"]2:10 PM[/TD]
[TD="align: right"]2.333[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: right"]8/13/2018[/TD]
[TD="align: right"]8:01 AM[/TD]
[TD="align: right"]12:01 PM[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: right"]8/13/2018[/TD]
[TD="align: right"]12:33 PM[/TD]
[TD="align: right"]4:05 PM[/TD]
[TD="align: right"]3.533[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: right"]8/14/2018[/TD]
[TD="align: right"]7:55 AM[/TD]
[TD="align: right"]12:01 PM[/TD]
[TD="align: right"]4.1[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: right"]8/14/2018[/TD]
[TD="align: right"]12:36 PM[/TD]
[TD="align: right"]4:14 PM[/TD]
[TD="align: right"]3.633[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: right"]8/15/2018[/TD]
[TD="align: right"]8:04 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]3.933[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: right"]8/15/2018[/TD]
[TD="align: right"]12:37 PM[/TD]
[TD="align: right"]4:01 PM[/TD]
[TD="align: right"]3.4[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: right"]8/16/2018[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD="align: right"]8/17/2018[/TD]
[TD="align: right"]8:30 AM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Employee Name​

[/TD]
[TD]

Employee Number​

[/TD]
[TD]

8/13/2018​

[/TD]
[TD]

8/14/2018​

[/TD]
[TD]

8/15/2018​

[/TD]
[TD]

8/16/2018​

[/TD]
[TD]

8/17/2018​

[/TD]
[TD]

8/18/2018​

[/TD]
[TD]

8/19/2018​

[/TD]
[TD]

Total Hours​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

M​

[/TD]
[TD]

T​

[/TD]
[TD]

W​

[/TD]
[TD]

Th​

[/TD]
[TD]

F​

[/TD]
[TD]

Sa​

[/TD]
[TD]

Su​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD]

7.55​

[/TD]
[TD]

9.633​

[/TD]
[TD]

5.267​

[/TD]
[TD]

8​

[/TD]
[TD]

8.417​

[/TD]
[TD]

2.333​

[/TD]
[TD]

0​

[/TD]
[TD]

41.2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

7.533​

[/TD]
[TD]

7.733​

[/TD]
[TD]

7.333​

[/TD]
[TD]

8​

[/TD]
[TD]

7.5​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

38.099​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

Employee Name​

[/TD]
[TD]

Employee #​

[/TD]
[TD]

Date​

[/TD]
[TD]

InTime​

[/TD]
[TD]

OutTime​

[/TD]
[TD]

Hours​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD]

8/13/2018​

[/TD]
[TD]

8:00 AM​

[/TD]
[TD]

12:00 PM​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD]

8/13/2018​

[/TD]
[TD]

12:30 PM​

[/TD]
[TD]

4:03 PM​

[/TD]
[TD]

3.55​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD]

8/14/2018​

[/TD]
[TD]

6:30 AM​

[/TD]
[TD]

4:08 PM​

[/TD]
[TD]

9.633​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD]

8/15/2018​

[/TD]
[TD]

11:40 AM​

[/TD]
[TD]

4:56 PM​

[/TD]
[TD]

5.267​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD]

8/16/2018​

[/TD]
[TD]

8:00 AM​

[/TD]
[TD]

4:00 PM​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD]

8/17/2018​

[/TD]
[TD]

7:35 AM​

[/TD]
[TD]

4:00 PM​

[/TD]
[TD]

8.417​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]Name1[/TD]
[TD]35EB600006[/TD]
[TD]

8/18/2018​

[/TD]
[TD]

11:50 AM​

[/TD]
[TD]

2:10 PM​

[/TD]
[TD]

2.333​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

8/13/2018​

[/TD]
[TD]

8:01 AM​

[/TD]
[TD]

12:01 PM​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

8/13/2018​

[/TD]
[TD]

12:33 PM​

[/TD]
[TD]

4:05 PM​

[/TD]
[TD]

3.533​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

8/14/2018​

[/TD]
[TD]

7:55 AM​

[/TD]
[TD]

12:01 PM​

[/TD]
[TD]

4.1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

8/14/2018​

[/TD]
[TD]

12:36 PM​

[/TD]
[TD]

4:14 PM​

[/TD]
[TD]

3.633​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

8/15/2018​

[/TD]
[TD]

8:04 AM​

[/TD]
[TD]

12:00 PM​

[/TD]
[TD]

3.933​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

8/15/2018​

[/TD]
[TD]

12:37 PM​

[/TD]
[TD]

4:01 PM​

[/TD]
[TD]

3.4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

8/16/2018​

[/TD]
[TD]

8:00 AM​

[/TD]
[TD]

4:00 PM​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]Name2[/TD]
[TD]35EB600092[/TD]
[TD]

8/17/2018​

[/TD]
[TD]

8:30 AM​

[/TD]
[TD]

4:00 PM​

[/TD]
[TD]

7.5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.