Excel Sum Formula

Question

I have a simple formula I am trying to run in excel. The data I have below is a total time, I am trying to add this data using =SUM(A1:A6) however this returns a value of 0:00:00 when it should add the values together. I have format on the cells of [h]:mm:ss

0:16:09
0:13:22
0:00:00
0:00:00
0:00:00
0:00:00

Total: 0:00:00

Not sure if this is something simple I am missing.

Answer 1

If your cell values are text then Excel will Sum to 0. So this may be your issue. You can use the TIMEVALUE function to convert these values to a numeric value that you can then sum up.

See below (F13:F14 is text):

Or you could just use an array formula such as =SUM(TIMEVALUE(F13:F14)) in this instance.

Remember, (Ctrl + Shift + Enter) when adding an array formula.

Answer 2

Just format the cells as Time (hh:mm:ss). It should look like this:

Answer 3

=SUMPRODUCT(A1:A5+0)

with the formatting of the cells as hh:mm:ss