How to Sum Time through SumIF

[devonknows]

Hi,

The problem i am facing at the moment is that a report i am using outputs raw data from a program. now i started to create an excel document to pull certain segments of the raw data. It works find up until i try and SUMIFS a time value. Now i know that in order for SUMIF to work u need to set a custom formatting on both the sum total box and the source data but that isnt an option as there will be several hundred different time fields mixed in with texts, percentage etc. P.S. I have also tried VLookup but becuase i need two criteria it wont work.

Now i did try this.

=INDEX('Raw Data'!$H:$H,MATCH(1,('Raw Data'!$A:$A="Total For Agent")*('Raw Data'!$B:$B=ShortID!$D11),0))

Which pulls the data fine but when i pull several rows of data then try and total it by means of SUM it doesnt seem to work.

Anyone got any ideas?

Regards,
Devon

 

[barry houdini]

Which range are you using? When you use +0 then that can only be applied to a value that can be converted to a number, that's why I didn't refer to the whole column - it won't work with a text header, for instance.

If you can chnage the source data then that would be best. You can't do it with formatting but this should work

Select column > Data > Text to columns > Finish

 

[devonknows]

Hi,

Im using the range that you supplied in the header the text i am looking for is within that range.

im thinking about sending the raw data i am trying to pull it from to you so you can look but i do not know how?

I tried the Text to Columns technique and still returns #VALUE with the +0 and 00:00:00 when i take i out

If i could send you the document maybe you could see but at the minute im at a loss with it all becuase i need to show this data and it needs to be for a low powered pc or the INDEX would work

Regards

 

[devonknows]

Hi,

Just to let you know i chose the VBA route to resolve this and for anyone that wants to know how to do this.

First i created a function inside a new module as shown here to loop through the column where the cells with the time value in to convert them into seconds

'Option Explicit

Public Sub ReformatTimeToSeconds()
    Dim Cell As Object, Rng As String
    
    'Set the Range
    Rng = "H1:H500"
    
    'Select the range for number reformat
    Range(Rng).Select
    
    'Reformat cells to [s] (Brackets prevent turning into minutes)
    Selection.NumberFormat = "[s]"
    
    'Loop Cells
    For Each Cell In Range(Rng)
        Cell.Value = Cell.Value
    Next Cell
    
    
End Sub

By doing it this way i change the cell format but you would have to click on the cell manually, click the formula bar and then click off and the cell would update. This changes the cell format, loops through the range and reset the cell value so Seconds ([h] Brackets to get a total seconds instead of seconds left after hours and minutes where calculated)

I then use my sum if formula to retrieve the data

=IF($B10="","",SUM(SUMIFS('Raw Data'!$H:$H,'Raw Data'!$B:$B,"=" & ShortID!$D5, 'Raw Data'!$A:$A,"=Total For Agent")))

• SUM Column H
• Column A = "Total For Agent"
• Column B = ShortID!$D5

Which out puts as: 0.1067361111111110000000
(times by 86400 to get ure actuall seconds, in this example is shows as 9222 seconds)

Then on my sheet where the above formula is, i set the Cell Format to: [h]:mm:ss, By changing the cell format on the sumif cell it now appears as this:

02:33:42
Working Out Code:
2*60*60 = 7200
33*60    = 1980
       +42 = 9222

Thanks for your help and i hope this helps others aswell

Regards