Sum function not working

Hi everyone,

I was trying to sum up a duration of time but excel is not letting me get the answer I need by using the Sum function. Please see below What I'm trying to achieve.

I have 2 sheets; the first sheet is the area where I analyse the data and the second sheet is where the data is coming from. From the second sheet say in column B2:B1000 is where the duration of time coming from each row in a format of 00:00:00.

In sheet 1, I will have a cell (any cell) where the formula is =sum('sheet2'!B2:B10000) but gives me a result of 00:00:00 even with changing the time format from nearly everything.

I managed to get the result by doing this formula =sum(sheet2'!B2:B1000*24) and that's fine.

But now the problem is, it will give me the Hours but not the minutes, instead it is showing as Hours then fraction of the hour as an example 4.93 as 4:55:38 (HH:MM:SS) as the total duration of time.

For me to achieve the format HH:MM:SS, I will need to apply a formula to another cell say =A2/24 to get the HH:MM:SS and format it.

My question is, is there a formula where I can sum up the duration of time without separately creating another cell to divide it by 24 just to get the format HH:MM:SS?

I have seen many tutorials that the sum function should work to add the times, but it never worked for me.

I managed to achieve it by doing the steps outlined above but trying to eliminate the last step where I need to add another cell to divide the total duration of time by 24 then format the cell to get HH:MM:SS.

Any help is highly appreciated.

Thanks in advance.

Maybe try =SUM(Sheet2!B2:B1000*24)/24 or --SUM(Sheet2!B2:B1000).

Hi everyone,

Thanks for looking into this.

MrShorty, I looked at the article and tried few things around but no luck. And also, it won't be a step I will go to if ever I managed to get things working as the idea from the source data is just to download, copy and paste to the template I'm working on as an easy way for the user to analyse the data.

Josephteh, the formula is working as per mentioned above but I need to put another in a different cell with time format then divide it by 24 to get the actual result I'm looking for as the sum function won't work if it done directly as you instructed too, It's giving me an answer of 00:00:00.

I have attached a sample file on what I'm trying to achieve.

On sheet Wk1 cell A2:1000 is data source which is formatted as HH:MM:SS.

Then in sheet named Data cell B6 I have the formula =SUM('Wk1'!A2:A10000*24) and gets the total sum which is correct but not in the format I need (I have tried all possible formatting to get the HH:MM:SS) please let me know if there's something I'm missing to correct.

In cell B7 I have the formula =B6/24 to get the format I require which is HH:MM:SS.

If I can eliminate the cell B7 to have the answer I need by just having the answer in cell B6, then it would be great.

Thanks in advance.

Hi AligW,

Thank you very much for your help.

=SUM('Wk1'!A2:A10000*24)/24 with the cell formatted to h:mm:ss is the solution I'm looking for.

I would have thought OP knows how to do cell formatting when he can specify the hh:mm:ss formatting.