counting sum Of Hours and minutes in Excel

[zaska]

Hello,

I have the following data and would like to count the sum of the same.

<table border="0" cellpadding="0" cellspacing="0" width="77"><col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:58pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid #95B3D7; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="77">Duration
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">02h 20m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7" height="20">03h 38m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">03h 42m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7" height="20">59m 45s</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">03h 02m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7" height="20">02h 44m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">01h 29m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7" height="20">05h 05m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">03h 43m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7" height="20">01h 20m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">06h 21m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7" height="20">01h 51m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">25m 00s</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7" height="20">01h 26m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">01h 32m</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #95B3D7" height="20">43m 27s</td> </tr> </tbody></table>
Thank you

 

[Aladin Akyurek]

What is expected as result with "count the sum of the same"?

 

[zaska]

Sir,

I would like to know the total sum of hours and minutes.

Thank you

 

[Aladin Akyurek]

Sir,

I would like to know the total sum of hours and minutes.

Thank you

One way...

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #4f81bd; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Duration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>h</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>m</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>s</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>02h 20m</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>500</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>72</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>40h 21m</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=64>03h 38m</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>03h 42m</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR></TBODY></TABLE>

C2, control+shift+enter, not just enter:

=SUM(IF(MID(A2:A17,3,1)="h",LEFT(A2:A17,2)+0))

D2, control+shift+enter, not just enter:

=SUM(IF(RIGHT(A2:A17)="m",LEFT(RIGHT(A2:A17,3),2)+0))+
  SUM(IF(MID(A2:A17,3,1)="m",LEFT(A2:A17,2)+0))

E2, control+shift+enter, not just enter:

=SUM(IF(RIGHT(A2:A17)="s",LEFT(RIGHT(A2:A17,3),2)+0))

F2, just enter:

=C2+INT(D2/60)&"h "&(MOD(D2,60)+INT(E2/60)&"m")

Seconds are dropped from the final sum in F2.