Reputation: 419
Excel Sum Formula
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.
Upvotes: 1
Views: 1772
Answers (3)
Reputation: 7303
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.
Upvotes: 3
Reputation: 419
=SUMPRODUCT(A1:A5+0)
with the formatting of the cells as hh:mm:ss
Upvotes: 0
Reputation: 4866
Just format the cells as Time (hh:mm:ss). It should look like this:
Upvotes: 0