SUMIF - Time Duration Issue?

Hello,

Attached is a spreadsheet that is recording machine downtime.
As per summary table inserted above the data in the first 4 tabs (we are focussing on A, B, C and D Service downtime). I would like to retrieve total downtime within each month.

Sadly I haven't been able to figure a formula that totals the time duration in column F (Total Down Time) as I believe it has transferred into excel as an incorrect format.
I have tried formatting to [h]:mm:ss, however this doesn't seem to fix the issue.

Any help greatly appreciated!!

Like the attached ? ( I only worked out the 1st tab)

Copy of Downtime Crunched.xlsx

Yes thankyou. I had shyed away from using a pivot table, but in this case it is effective.

It can also be done by formulas if you prefer

If possible could you do one with formulas?
Just to further increase my understanding.

Thanks so much.

For the 2nd tab

Copy of Downtime Crunched.xlsx

Hi JoshEvo

I've had problems like this in the past too - very frustrating!!

The only way I can solve this issue is to insert another custom formatted column ([h]:mm:ss) and, in your example, use this formula to convert the 'text' into a proper time:

[code]=VALUE(LEFT(F9,(SEARCH(":",F9)-1)) & ":" & (MID(F9,SEARCH(":",F9)+1,2)) & ":" & (RIGHT(F9,2)))code]

This is copied down through the new column.

A second new custom formatted column ([h]:mm:ss) is needed, into which you can copy the Values only from the first new column. These can then be copied over the original values and the two new columns deleted.

I have attached an example, based on your first tab, and haver it totalled in your summary.

This method threw up one error, based on your data, in row 221, because the initial value is '39:25:60', so I resolved this by replacing it with '39:26:00'

If anyone has a better solution, and I'm sure there has to be one, please post!!

In the meantime, hopefully this helps.

Regards
Stephen.

I have now completed this for all of the sheets and it worked no problems.
Any input from others would be helpful for future however.

Stephen,

To offer two solutions, would you be able to guide me through the pivot table creation?
I receive a #DIV/0 error when trying to re-create, it occurs when I try and apply the average field setting to "TOTAL DOWN TIME".

I understand the pivot table will be much easier to keep updated in the future than the formula method.

Thank you so much for your help.