Time count based on condition

Question

I've been trying hard to figure this one out, see if anyone could give me some directions, please.

I have a worksheet in which I put the activities performed through the day (column W), so I have a report for every day. Each activity has a type defined by a letter listed in a drop down menu (column U), for example: 'R' for 'Reporting', 'M' for 'Meeting' etc, and a duration in hours (HH:mm) (column S).

What I want my worksheet to do is to sum all the durations of the activities based on their type and store the results in a diferent cell. In the image example attached, I'd have to have 3:30 h for Meeting, 4:30 for Reporting and 2 h for Drawing.

What the following SumIf code does is to sum all the hours, but it is not acounting for the duration, once the duration is the difference between the time in the next row and the time of the row in which the conditional argument is.

Sub test_count_hours()

Range("E35").Value = WorksheetFunction.SumIf(Range("U8:U37"), "R", Range("S8:S37"))

End Sub

So my struggle is to make the code acounting for the duration of the events.

Example of a filled Worksheet

Answer 1

How about storing your activity types in an array? You can loop through each activity and then loop through each row to check the activity type, adding the time between that and the next row if it's a match

Sub sumHours()
Dim activityList As Variant
Dim activity As Variant
Dim rowCount As Integer
Dim countHours As Date

activityList = Array("M", "R", "Dv")

Range("U5").Select
Range(Selection, Selection.End(xlDown)).Select
rowCount = Selection.Rows.Count

For Each activity In activityList
countHours = 0
    For i = 5 To rowCount + 5
        If Range("U" & i) = activity Then
            countHours = countHours + Range("S" & i + 1) - Range("S" & i)
        End If
    Next i

    Select Case activity
        Case Is = "M"
        Range("E35") = countHours

        Case Is = "R"
        Range("E36") = countHours

        Case Is = "Dv"
        Range("E37") = countHours
    End Select
Next activity
End Sub

(Make sure the cells that will hold the timevalues are formatted with the "Time" data type.