Originally Posted by

**Miratshah**
Okay. You can do that.

Row 264 for Job 8111863 for amount 62,500 is marked as Contra. Similarly Job numbers 8111939, 8111958 and 8111773-13. All have just one line however marked as Contra.

I changed the limit to 10 from 5. Post that If I select only Contra marked transactions, I get total of 95,032 due to Jobs mentioned in point number 2.

I think once you execute point 1, WIP will be wiped off with X. So we can ignore this. All WIP for this point are also marked as X.

Ok, try this:

Code:

Sub a1086996e()
*'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html*
Dim i As Long, j As Long, n As Long
Dim x As Long, k As Long, z As Long
Dim q As Long, LMT As Long
Dim va, vb, vc
Dim flag As Boolean, oneline As Boolean
Application.ScreenUpdating = False
*'Change this to suit*
LMT = 10 *'LMT is the limit (plus & minus) where you considered the sub total of each data section to be the same as zero*
n = Range("C" & Rows.count).End(xlUp).Row
va = Range("C1:C" & n)
vb = Range("K1:K" & n)
ReDim vc(1 To n, 1 To 3)
Range("L1:N" & n).ClearContents
For i = 2 To UBound(va, 1)
j = i: x = 0
Do
x = x + vb(i, 1)
i = i + 1
If i > UBound(va, 1) Then Exit Do
Loop While va(i, 1) = va(i - 1, 1)
i = i - 1
vc(i, 2) = x
flag = False
If x >= -LMT And x <= LMT Then flag = True: GoTo skip:
oneline = False
If j = i Then oneline = True: flag = True: GoTo skip:
If x < 0 Then
For k = j To i
vb(k, 1) = vb(k, 1) * -1
Next
x = x * -1
End If
For k = j To i
z = 0
For q = j To k
z = z + vb(q, 1)
Next
flag = False
If z = x Then
vc(k, 1) = 1: vc(k, 1) = "WIP": flag = True: GoTo skip:
ElseIf z > x Then
vb(k, 1) = 0
Else
If vb(k, 1) <= 0 Then
vb(k, 1) = 0
Else
vc(k, 1) = "WIP"
End If
End If
Next
skip:
If flag = False Then
For k = j To i
vc(k, 1) = "X"
Next
Else
For k = j To i
If vc(k, 1) <> "WIP" Then vc(k, 1) = "CONTRA": flag = False
If oneline = True Then oneline = False: vc(k, 1) = "WIP"
Next
End If
Next
Range("L1").Resize(UBound(vc, 1), 3) = vc
Range("L1") = "By Macro"
Application.ScreenUpdating = True
End Sub

## Like this thread? Share it with others