Sum of Time Based on Criteria

[Maddy1234]

Hi experts,

Another problem with my last data set. I have the following data set where the users take part in 4 exercises.

[TABLE="width: 297"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Time Started[/TD]
[TD]Time Ended[/TD]
[/TR]
[TR]
[TD]Exercise 1[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]20:00[/TD]
[/TR]
[TR]
[TD]Exercise 2[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]21:00[/TD]
[/TR]
[TR]
[TD]Exercise 3[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]22:00[/TD]
[/TR]
[TR]
[TD]Exercise 1[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]23:00[/TD]
[/TR]
[TR]
[TD]Exercise 4[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]0:00[/TD]
[/TR]
[TR]
[TD]Exercise 2[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]1:00[/TD]
[/TR]
[TR]
[TD]Exercise 3[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]2:00[/TD]
[/TR]
[TR]
[TD]Exercise 1[/TD]
[TD="align: right"]2:00[/TD]
[TD="align: right"]3:00[/TD]
[/TR]
[TR]
[TD]Exercise 4[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]4:00[/TD]
[/TR]
[TR]
[TD]Exercise 3[/TD]
[TD="align: right"]4:00[/TD]
[TD="align: right"]5:00[/TD]
[/TR]
[TR]
[TD]Exercise 2[/TD]
[TD="align: right"]5:00[/TD]
[TD="align: right"]6:00
[/TD]
[/TR]
</tbody>[/TABLE]

The result I need is as follows.

[TABLE="width: 198"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]Total Time[/TD]
[/TR]
[TR]
[TD]Exercise 1[/TD]
[TD="align: right"]3:00:00[/TD]
[/TR]
[TR]
[TD]Exercise 2[/TD]
[TD="align: right"]3:00:00[/TD]
[/TR]
[TR]
[TD]Exercise 3[/TD]
[TD="align: right"]3:00:00[/TD]
[/TR]
[TR]
[TD]Exercise 4[/TD]
[TD="align: right"]2:00:00
[/TD]
[/TR]
</tbody>[/TABLE]

I tried to achieve this result by adding a helper column to the data set and getting the time differences and then doing a SUMIF in the Total Time column. It works for smaller data sets but not for larger sets of data. Hence Im looking for a solution without the use of a helper column.

I only need help with the column "Total Time".

Many thanks.

 

[Marcelo Branco]

Maybe...

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

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

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

Time Started​

[/TD]
[TD]

Time Ended​

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

Total Time​

[/TD]
[TD]

Total (decimal)
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Exercise 1​

[/TD]
[TD]

19:00​

[/TD]
[TD]

20:00​

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

Exercise 1​

[/TD]
[TD]

03:00​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Exercise 2​

[/TD]
[TD]

20:00​

[/TD]
[TD]

21:00​

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

Exercise 2​

[/TD]
[TD]

03:00​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Exercise 3​

[/TD]
[TD]

21:00​

[/TD]
[TD]

22:00​

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

Exercise 3​

[/TD]
[TD]

03:00​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Exercise 1​

[/TD]
[TD]

22:00​

[/TD]
[TD]

23:00​

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

Exercise 4​

[/TD]
[TD]

02:00​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Exercise 4​

[/TD]
[TD]

23:00​

[/TD]
[TD]

00:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Exercise 2​

[/TD]
[TD]

00:00​

[/TD]
[TD]

01:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Exercise 3​

[/TD]
[TD]

01:00​

[/TD]
[TD]

02:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Exercise 1​

[/TD]
[TD]

02:00​

[/TD]
[TD]

03:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Exercise 4​

[/TD]
[TD]

03:00​

[/TD]
[TD]

04:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Exercise 3​

[/TD]
[TD]

04:00​

[/TD]
[TD]

05:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Exercise 2​

[/TD]
[TD]

05:00​

[/TD]
[TD]

06:00​

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


Formula in F2 copied down
=SUMPRODUCT(--(A$2:A$12=E2),MOD(C$2:C$12-B$2:B$12,1))

Format F2:F6: Custom [hh]:mm

or to get the total as decimal value
Formula in G2 copied down
=SUMPRODUCT(--(A$2:A$12=E2),MOD(C$2:C$12-B$2:B$12,1))*24

M.

 

[Maddy1234]

Thanks Marcelo. Works like a charm.

Maybe...

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

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

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

Time Started​

[/TD]
[TD]

Time Ended​

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

Total Time​

[/TD]
[TD]

Total (decimal)
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Exercise 1​

[/TD]
[TD]

19:00​

[/TD]
[TD]

20:00​

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

Exercise 1​

[/TD]
[TD]

03:00​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Exercise 2​

[/TD]
[TD]

20:00​

[/TD]
[TD]

21:00​

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

Exercise 2​

[/TD]
[TD]

03:00​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Exercise 3​

[/TD]
[TD]

21:00​

[/TD]
[TD]

22:00​

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

Exercise 3​

[/TD]
[TD]

03:00​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Exercise 1​

[/TD]
[TD]

22:00​

[/TD]
[TD]

23:00​

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

Exercise 4​

[/TD]
[TD]

02:00​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Exercise 4​

[/TD]
[TD]

23:00​

[/TD]
[TD]

00:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Exercise 2​

[/TD]
[TD]

00:00​

[/TD]
[TD]

01:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Exercise 3​

[/TD]
[TD]

01:00​

[/TD]
[TD]

02:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Exercise 1​

[/TD]
[TD]

02:00​

[/TD]
[TD]

03:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Exercise 4​

[/TD]
[TD]

03:00​

[/TD]
[TD]

04:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Exercise 3​

[/TD]
[TD]

04:00​

[/TD]
[TD]

05:00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Exercise 2​

[/TD]
[TD]

05:00​

[/TD]
[TD]

06:00​

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


Formula in F2 copied down
=SUMPRODUCT(--(A$2:A$12=E2),MOD(C$2:C$12-B$2:B$12,1))

Format F2:F6: Custom [hh]:mm

or to get the total as decimal value
Formula in G2 copied down
=SUMPRODUCT(--(A$2:A$12=E2),MOD(C$2:C$12-B$2:B$12,1))*24

M.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.