CODEWITHSUNDEEP
vbaexcelmacros
Izzy Schneider
Izzy Schneider

Reputation: 17

VBA Repeatd summing up of hours until certain criterion

I have a table with 6 columns. [ID, Status, Start Time, End Time, Hours, Sum UP]

Table Example

I have counted a total amount hours between Start Time and End Time. Now I have to count a Sum Up of this hours. The problem is, that the sum up must be counted in a special range, starting from status "Shipped" until status "Checked" appears for only the first time. Then repeat starting again with "Shipped" until "Checked" for the next ID.

Sub SUMUP()
   Dim LastRow As Long
   LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

   For i = 2 To LastRow
      Cells(i, 6).Value = WorksheetFunction.SumIf(Range("A2:A" & LastRow), Range("A" & i), Range("E2:E" & LastRow))

  Next

End Sub

I have this code to sum up a total amount of hours. How can I write a VBA code for my case?

Thank you in advance for your help.

Upvotes: 1

Views: 133

Answers (4)

Tom Sharpe
Tom Sharpe

Reputation: 34440

Just for completeness, I believe this non-array formula should work:-

=IF(AND(B2="checked",COUNTIFS(A$1:A2,A2,B$1:B2,"checked")=1),C2-INDEX(C$2:C$10,MATCH(A2&"shipped",INDEX(A$2:A$12&B$2:B$12,0),0)),"")

It just subtracts shipped datetime from first checked datetime.

Multiply by 24 to get the time in hours.

Upvotes: 0

Mister 832
Mister 832

Reputation: 1221

you could use this formula, if the order Status is sorted in the correct order:

=SUMIF(INDIRECT("A"&MATCH(A2,A:A,0)&":A"&ROW(A2)),A2,INDIRECT("E"&MATCH(A2,A:A,0)&":E"&ROW(A2)))

This would be an vba solution

Sub SUMUP()
    Dim LastRow As Long
    Dim hours As Integer
    Dim ID As Integer
    Dim checked As Boolean

    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    ID = Cells(2, 1).Value
    For i = 2 To LastRow
         If ID = Cells(i, 1).Value Then
             If Cells(i, 2).Value <> "checked" And checked = False Then
                 hours = Cells(i, 6).Value + hours
                 Cells(i, 10).Value = hours
             ElseIf Cells(i, 2).Value = "checked" And checked = False Then
                 checked = True
                 hours = Cells(i, 6).Value + hours
                 Cells(i, 10).Value = hours
             End If
         Else

             ID = Cells(i, 1).Value
             checked = False
             hours = 0
         End If
    Next

End Sub

Upvotes: 0

Andreas
Andreas

Reputation: 23968

Try this one:

Sub SUMUP()
    Dim LastRow As Long
    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow
        If Range("B" & i).Value = "Shipped" Then
            For j = i To LastRow
                If Range("B" & j).Value <> "Checked" Then
                    Sum = Sum + Range("E" & j).Value
                Else
                    Range("F" & j).Value = Sum
                    Exit For
                End If
            Next j
            i = j
            Sum = 0
        End If
    Next i

End Sub

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

or an array 

=SUM(INDIRECT(("f"&MIN(IF((($A$1:$A$10="ID")*($B$1:$B$10="shipped")),ROW($A$1:$A$10)))&":"&"f"&MIN(IF((($A$1:$A$10=1)*($B$1:$B$10="checked")),ROW($A$1:$A$10))))))

Upvotes: 0

Related Questions