Getting SUM for hh mm ss in EXCEL

Question

In a document I'm working on, I need to find the SUM of different call time values and assign or display that amount in a total cell. I've included a picture of the data I'm using. The cell receiving the entries is formatted in [hh]:mm:ss, and the entries are in the hh:mm:ss format. The equation is =SUM (C2:C11). Despite adding braces to the format, the totals cell still displays a value of 00:00:00. Can somebody assist me with this? Thank you very much.

Answer 1

The times are probably preserved as text.

SUM will disregard the text, so it will return 0.

One possibility is to change the text to a real time, but since the time is already in a format that Excel can understand as a time, we can do it in the formula:

=SUMPRODUCT(--C2:C11)

The -- will force the text into a number and the SUMPRODUCT will iterate the array and sum the results.

With Office 365 we can use SUM:

=SUM(--C2:C11)

Older versions will need to use Ctrl-Shift-Enter if SUM is used instead of SUMPRODUCT which will not require it in any version.