Search code examples
google-sheetsarray-formulasgoogle-sheets-formula

Sum multiple TIME()'s

I have this table of scores:

---------
| TIME  | 
---------
| L4:10 |
| W2:32 |
| L2:23 |
| L6:10 |
| W1:10 |
---------

To convert a score to an actual time, I use:

=TIME(0, MID(A1, 2, 1), MID(A1, 4, 2))  [ which outputs 00:04:10 for the A1 ]

Is there any way to sum all these totals at once without creating new cells?

(Note: I cannot remove the L's and W's from the original cells)

Solution

  • Consider wrapping it up into ARRAYFORMULA this way:

    =ARRAYFORMULA(SUM(TIME(0, MID(A1:A5, 2, 1), MID(A1:A5, 4, 2))))

    This will sum all the values in A1:A5 range into one cell.