Finding the Sum of Zero with multiple variables

W

westc4

Board Regular
Joined
Aug 1, 2012
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Hello experts!

I am in need of a formula that will help me find when two or more documents offset to zero. I have a large table of POs, Customers, and the open amounts of the documents.

What I have attempted was a simple =IF(AND(C2=C3,F2=F3),SUM(F2:F3),"") this worked to find the first document of a set that would equal zero, but I need to locate all associated documents that equal to zero.

Book1
ABC
1POCustomerAmount
22531761109817(4,850.00)
349880311098174,575.00
44988031109817(4,575.00)
558083911098174,475.00
65808391109817(9,325.00)
764857711098177,462.50
86485771109817(7,462.50)
964917611098179,975.00
106491761109817(9,975.00)
1180677411098174,975.00
128067741109817(4,975.00)
1387870711098174,925.00
148787071109817(4,925.00)
1545224311161861,911.00
164522431116186(1,911.00)
171623341126360624.00
181719391126360(1,062.00)
19113762112636060,900.00
201137621126360(43,500.00)
21DAMAGED /CUST 110781811263600.00
22DAMAGED /CUST 110781811263600.00
23DAMAGED RETURN/CUST 110781811263600.00
241536011131193(660.00)
251536011131193(237.00)
261536011131193(696.00)
2731988511311933,185.00
283198851131193(3,185.00)
293198851131193397.00
3031988511311933,185.00
313198851131193794.00
323198851131193794.00
333198851131193397.00
343198851131193(2,730.00)
Sheet1


Thank you for your help!!
Cari
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Your data sample doesn't match your formula, are we to assume that the amounts in column C of the sample are in column F of your actual data? If this is the case then does column C in the formula relate to PO or Customer in the data?

It would help to eliminate any confusion if you could also add your expected results to the example.
 
Upvote 0
This code is adapted from a previous code I've written for someone else. See if this does what you want.

VBA Code: 
Sub RemoveNetZero()
    Dim a, i As Long, j As Long, k As Long, t As Double
    
    a = Range("C2").CurrentRegion.Value
    
    For i = 2 To UBound(a)
        If a(i, 3) <> "" Then t = a(i, 3)
        For j = i + 1 To UBound(a)
            If a(j, 3) <> "" Then
                If (t + a(j, 3)) = 0 Then
                    For k = 1 To UBound(a, 2)
                        a(i, k) = ""
                        a(j, k) = ""
                    Next
                    '
                    'This part highlights the Net zero rows -- remove if unnecessary
                    '---------------------------------------------------------------
                    Cells(i, 1).Resize(, UBound(a, 2)).Interior.Color = vbYellow
                    Cells(j, 1).Resize(, UBound(a, 2)).Interior.Color = vbYellow
                    '---------------------------------------------------------------
                    '
                    Exit For
                End If
            End If
        Next
    Next
    
    '
    'This part adds a new sheet and output all Rows with the Net zero rows removed -- remove if unnecessary
    '---------------------------------------------------------------
    With Sheets.Add
        .Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
        With .UsedRange
            .Sort .Columns("B"), xlAscending, Header:=xlYes
        End With
        .Columns.AutoFit
    End With
    '---------------------------------------------------------------
    '
End Sub
 
Upvote 0
jasonb75 said:
Your data sample doesn't match your formula, are we to assume that the amounts in column C of the sample are in column F of your actual data? If this is the case then does column C in the formula relate to PO or Customer in the data?

It would help to eliminate any confusion if you could also add your expected results to the example.
Click to expand...
I am sorry, I simplified the table. The formula that I attempted would now be IF(AND(A2=A3,B2=B3),SUM(C2:C3),"") What I am attempting to do is identify the lines that go together and equal zero. Some PO will have multiple lines and some will only have two, but they will not always equal zero.

I was hoping for a formula in column D that would show when POs equaled zero, as the table is sorted by PO. If there is a Zero in column D then I know those rows all can be offset to Zero.
 
Upvote 0
aRandomHelper said:
This code is adapted from a previous code I've written for someone else. See if this does what you want.

VBA Code: 
Sub RemoveNetZero()
    Dim a, i As Long, j As Long, k As Long, t As Double
   
    a = Range("C2").CurrentRegion.Value
   
    For i = 2 To UBound(a)
        If a(i, 3) <> "" Then t = a(i, 3)
        For j = i + 1 To UBound(a)
            If a(j, 3) <> "" Then
                If (t + a(j, 3)) = 0 Then
                    For k = 1 To UBound(a, 2)
                        a(i, k) = ""
                        a(j, k) = ""
                    Next
                    '
                    'This part highlights the Net zero rows -- remove if unnecessary
                    '---------------------------------------------------------------
                    Cells(i, 1).Resize(, UBound(a, 2)).Interior.Color = vbYellow
                    Cells(j, 1).Resize(, UBound(a, 2)).Interior.Color = vbYellow
                    '---------------------------------------------------------------
                    '
                    Exit For
                End If
            End If
        Next
    Next
   
    '
    'This part adds a new sheet and output all Rows with the Net zero rows removed -- remove if unnecessary
    '---------------------------------------------------------------
    With Sheets.Add
        .Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
        With .UsedRange
            .Sort .Columns("B"), xlAscending, Header:=xlYes
        End With
        .Columns.AutoFit
    End With
    '---------------------------------------------------------------
    '
End Sub
Click to expand...
Thank you for this code. It worked for most of the table but is it also picking up rows where the PO is the same but does not equal zero. In this example, two of the lines would be zero, but the third line should not highlight.

Book3
ABC
1POCustomer Amount
2916486EXCHANGE1142501397.00
3916486EXCHANGE1142501397.00
4916486EXCHANGE1142501(397.00)
Sheet1
 
Upvote 0
Is this on the right track?

Might need a bit of fine tuning looking at rows 27, 28 and 30 but want to be sure that it is what you want first given that you didn't provide the expected results in your example.
Book1
ABCD
1POCustomerAmountZero?
22531761109817-4850-4850
3498803110981745750
44988031109817-45750
5580839110981744754475
65808391109817-9325-9325
764857711098177462.50
86485771109817-7462.50
9649176110981799750
106491761109817-99750
11806774110981749750
128067741109817-49750
13878707110981749250
148787071109817-49250
15452243111618619110
164522431116186-19110
171623341126360624624
181719391126360-1062-1062
1911376211263606090060900
201137621126360-43500-43500
21DAMAGED /CUST 1107818112636000
22DAMAGED /CUST 1107818112636000
23DAMAGED RETURN/CUST 1107818112636000
241536011131193-660-660
251536011131193-237-237
261536011131193-696-696
27319885113119331850
283198851131193-31853185
293198851131193397397
30319885113119331850
313198851131193794794
323198851131193794794
333198851131193397397
343198851131193-2730-2730
Sheet5
Cell Formulas
RangeFormula
D2:D34D2=C2+SUMIFS(C:C,A:A,A2,B:B,B2,C:C,-C2)


Also, Please update your profile to show which version of excel you are using. There are functions in newer versions that might make the task easier but we do not know if you have them.
 
Last edited:
Upvote 0
westc4 said:
In this example, two of the lines would be zero, but the third line should not highlight.
Click to expand...
Are you sure there isn't another -397 below that? Because the code should be taking care of that.

Anyway, I've made a slight change to make sure the PO number should match too.
VBA Code: 
Sub RemoveNetZero()
    Dim a, i As Long, j As Long, k As Long, t As Double
    
    a = Range("C2").CurrentRegion.Value
    
    For i = 2 To UBound(a)
        If a(i, 3) <> "" Then t = a(i, 3)
        For j = i + 1 To UBound(a)
            If a(j, 3) <> "" Then
                If (t + a(j, 3)) = 0 And a(i, 1) = a(j, 1) Then
                    For k = 1 To UBound(a, 2)
                        a(i, k) = ""
                        a(j, k) = ""
                    Next
                    '
                    'This part highlights the Net zero rows -- remove if unnecessary
                    '---------------------------------------------------------------
                    Cells(i, 1).Resize(, UBound(a, 2)).Interior.Color = vbYellow
                    Cells(j, 1).Resize(, UBound(a, 2)).Interior.Color = vbYellow
                    '---------------------------------------------------------------
                    '
                    Exit For
                End If
            End If
        Next
    Next
    
    '
    'This part adds a new sheet and output all Rows with the Net zero rows removed -- remove if unnecessary
    '---------------------------------------------------------------
    With Sheets.Add
        .Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
        With .UsedRange
            .Sort .Columns("B"), xlAscending, Header:=xlYes
        End With
        .Columns.AutoFit
    End With
    '---------------------------------------------------------------
    '
End Sub
 
Upvote 0
This looks to be more accurate, I've simply copied the value from column C while testing but that can be easily changed to whatever is preferred.
Book1
ABCD
1POCustomerAmountZero?
22531761109817-4850-4850
3498803110981745750
44988031109817-45750
5580839110981744754475
65808391109817-9325-9325
764857711098177462.50
86485771109817-7462.50
9649176110981799750
106491761109817-99750
11806774110981749750
128067741109817-49750
13878707110981749250
148787071109817-49250
15452243111618619110
164522431116186-19110
171623341126360624624
181719391126360-1062-1062
1911376211263606090060900
201137621126360-43500-43500
21DAMAGED /CUST 1107818112636000
22DAMAGED /CUST 1107818112636000
23DAMAGED RETURN/CUST 1107818112636000
241536011131193-660-660
251536011131193-237-237
261536011131193-696-696
27319885113119331850
283198851131193-31850
293198851131193397397
30319885113119331853185
313198851131193794794
323198851131193794794
333198851131193397397
343198851131193-2730-2730
Sheet5
Cell Formulas
RangeFormula
D2:D34D2=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)<=COUNTIFS(A:A,A2,B:B,B2,C:C,-C2),0,C2)
 
Upvote 0
Solution
Thank you! Both the VBA and the formula work for what I was looking for.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top