Summing up TIME - 2 Tables

[Shahria]

Hi All. I'm attempting to sum up a list of times, from two tables IF their names match. Daily i go through a long list and + them manually. Tried to mix IF statements and VLOOK UPS and didn't get anywhere with it. Can anyone help me?

[TABLE="width: 120"]
<tbody>[TR]
[TD]Sam[/TD]
[TD]00:39:36[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]00:38:05[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]01:20:53[/TD]
[/TR]
[TR]
[TD]Pete[/TD]
[TD]00:12:06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]00:45:51[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]00:12:06[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

maybe something like this with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Time[/td][td][/td][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Time[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Time[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sam[/td][td=bgcolor:#DDEBF7]

00:39:36​

[/td][td][/td][td=bgcolor:#DDEBF7]Sam[/td][td=bgcolor:#DDEBF7]

00:45:51​

[/td][td][/td][td=bgcolor:#E2EFDA]Sam[/td][td=bgcolor:#E2EFDA]

01:25:27​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Kate[/td][td]

00:38:05​

[/td][td][/td][td]Kate[/td][td]

00:12:06​

[/td][td][/td][td]Kate[/td][td]

00:50:11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Dan[/td][td=bgcolor:#DDEBF7]

01:20:53​

[/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Dan[/td][td=bgcolor:#E2EFDA]

01:20:53​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pete[/td][td]

00:12:06​

[/td][td][/td][td][/td][td][/td][td][/td][td]Pete[/td][td]

00:12:06​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Table.Combine({Table1, Table2}),
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Time", each List.Sum([Time]), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Time", type time}})
in
    #"Changed Type"[/SIZE]

 

[Jon von der Heyden]

Or using Excel formulas

Excel 2010
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Time[/td][td][/td][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Time[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Time[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td=bgcolor:#DDEBF7]Sam[/td][td=bgcolor:#DDEBF7]

00:39:36​

[/td][td][/td][td=bgcolor:#DDEBF7]Sam[/td][td=bgcolor:#DDEBF7]

00:45:51​

[/td][td][/td][td=bgcolor:#E2EFDA]Sam[/td][td=bgcolor:#E2EFDA]

01:25:27​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td=bgcolor:#FFFFFF]Kate[/td][td=bgcolor:#FFFFFF]

00:38:05​

[/td][td][/td][td=bgcolor:#FFFFFF]Kate[/td][td=bgcolor:#FFFFFF]

00:12:06​

[/td][td][/td][td=bgcolor:#FFFFFF]Kate[/td][td=bgcolor:#FFFFFF]

00:50:11​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td=bgcolor:#DDEBF7]Dan[/td][td=bgcolor:#DDEBF7]

01:20:53​

[/td][td][/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#FFFFFF][/td][td][/td][td=bgcolor:#E2EFDA]Dan[/td][td=bgcolor:#E2EFDA]

01:20:53​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td=bgcolor:#FFFFFF]Pete[/td][td=bgcolor:#FFFFFF]

00:12:06​

[/td][td][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][td][/td][td=bgcolor:#FAFAFA][/td][td=bgcolor:#FAFAFA][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Formula in H2 copied down to H4: =SUMIF(A:A,G2,B:B)+SUMIF(D:D,G2,E:E)