Sum of a number of column values until a value of another column is equal to a specific value.

[janpol]

Hi everyone,
I am trying to complete the following as a VBA loop for a production plan: The values in column A need to be summed for every occurrence of 40 in column B. The result is going to appear in column C as the total of all values until the next occurrence of 40. For example 2 + 12+ 4 = 18 appears in cell C1, and 8+15 = 23 appears in cell C4. It looks simple but somehow my approaches do not work.

 

[earthworm]

In column C add below formula
=IF(B1=40,SUMIF($D$1:$D$12,COUNTIF($B$1:B1,B1),$A$1:$A$12),0)

In Column D add below formula
=COUNTIF(B$1:B1,40)

For sample excel click Here

 

[janpol]

Thank you very much! Do you think it is efficient to record this as macro for large number of records or can this be done in VBA in a For loop?

 

[earthworm]

Thank you very much! Do you think it is efficient to record this as macro for large number of records or can this be done in VBA in a For loop?

how large is your data . like count.

 

[Peter_SSs]

Would this be sufficient?

Sub Total_40()
  Dim lr As Long
  
  lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("C1:C" & lr)
    .FormulaR1C1 = "=IF(RC[-1]=40,SUM(RC[-2]:R" & lr + 1 & "C[-2])-SUM(R[1]C:R" & lr + 1 & "C),0)"
    .Value = .Value
  End With
End Sub

 

[janpol]

Both suggestions worked perfect, thank you! I tried it myself too, it works for most but it is not calculating the last total. The loop may need a small adjustment. I would appreciate if you are able to spot the mistake.

Sub Macro1()
Dim a(1 To 12) As Long
Dim b(1 To 12) As Long
Dim c(1 To 12) As Long
Dim m As Long
Dim i As Integer
Dim j As Integer
Dim x As Integer


For j = 1 To 12
    a(j) = ActiveSheet.Cells(j, 1)
    b(j) = ActiveSheet.Cells(j, 2)
Next j

c(1) = 40
x = 0

For j = 2 To 12

    If b(j) > b(j - 1) Then
        m = 0
        For i = x + 1 To j - 1
            m = m + a(i)
            c(x + 1) = m
            If j = 12 Then
              c(j) = a(j)
            End If
        Next i
        x = j - 1
    End If

Next j

For j = 1 To 12
ActiveSheet.Cells(j, 3) = c(j)
Next j

End Sub

 

[Peter_SSs]

I would appreciate if you are able to spot the mistake.

Your code is a very long-winded way to read in the data & get the results but the (or at least one) error is this (assuming that column B reduces until it jumps back to 40 which appears to be the case from your code)

For j = 2 To 12

    If b(j) > b(j - 1) Then
        m = 0
        For i = x + 1 To j - 1
            m = m + a(i)
            c(x + 1) = m
            If j = 12 Then
              c(j) = a(j)
            End If
        Next i
        x = j - 1
    End If

Next j

When j gets to 12
b(12) > B(11) will always be false, unless b(12) = 40
Therefore the code never gets to the amber code when j = 12

You can usually figure out things like that for yourself if you step through your code with the F8 key, noting what happens at each step.

 

[Peter_SSs]

If you wanted to get those results with a looping macro, here is a more efficient way.

Sub SumAt40()
  Dim a As Variant
  Dim b(1 To 12) As Long
  Dim i As Long
  Dim m As Long
  
  a = Range("A1:B12").Value
  For i = UBound(a) To 1 Step -1
    m = m + a(i, 1)
    If a(i, 2) = 40 Then
      b(i) = m
      m = 0
    End If
  Next i
  Range("C1:C12").Value = Application.Transpose(b)
End Sub

 

[janpol]

Thank you so much! I have taken Basic and Cobol decades ago, my coding skills are poor.

 

[Peter_SSs]

You're welcome.