Sum if and Equal To

No the the 85 and -85 are in the same column

Here is the data

Here is a example of what the outcome should reflect. I may be missing some that tie to zero but you will get the picture

1=one to one [Amounts] and highlight green
2= One to Many [Amounts] and highlight green

Run the first formula first that lables them "1"
Run the formula excluding [Amounts] that have a "1" and sum to zero and label "2"

So what everything is done. When you filter by AB by 1 and 2 you will get all data that nets to zero

I apologize here is the data attached with the formula. I am trying to grab all that tie and net zero one for one and one to many. In the previous reponse i was trying to use another logic with this formula [code][=IF(SUMIF($B$2:$B$668,B2,$E$2:$E$668)=0,1,IF(ISERROR(MATCH(-E2,$E$2:$E$668,FALSE)),"",2))][code]

This formula is in the spread sheet ending with v1-2

File ending with Formulas- out of the pivot table data, the formula is only grabing what sum to zero by one to many. It does not pick up the 0ne for one values Like 86.00 and a -86.00.

The additional Spreadsheet
I was thinking of a macro that will per the [Operation] will or if the operation is the same
1. Match amounts when added together = 0 (86, -86) and label them "1"In column AB
2 Then If operation is the same and if there is not a "1" in Column AB sum all the other Amounts that may equal one to many For Example ( 1,078.55+ 9,670.00 = 10,748.55) and label "2 "in Column AB
3. Highlight the Amount Cells Green if there is a 1 or 2 in Column AB

So i attempted [code] =IF(SUMIFS([Amount],[OPERATION],[@OPERATION])=0,"Zero")[code] and it did not work. Whati am trying to accomplish relates only to finding everything in the spreadsheet that = zero and highlight them. Wether it is 1=1 or 1.2.3.=1 in the same Operations. The above examples and statements are different ways i was thinking of how to get to my ending results. My ultimate goal was to create that macro above in that order and need help. I was thinking if you can create a labeler and then in the second eqution and eliminate the first by its results you will be able to pull all amount that tie zero that do not relate to the data that has "1"

any suggestions

First, let me say thank you for the formula. It works perfectly.


For the second statement, i apologize. The Target sum is zero.
So is there a way to fist say [code]=IF(SUMPRODUCT(($C$2:$C$1000=C2)*(ABS($G$2:$G$1000)=ABS(G2)))=2,1,"")[code] then say, sum if Operation is the same excludinge if there is a 1 in V that then ties to zero.

Basically what i am tyring to say is give me now the values that are one to many that tie to zero. I am excluding the one to one becauase now they can not used againg. For example using the below numbers if the amounts in a operation are the same, Exclude the 50's and sum the 20 and the 80 that ties or net to zero and label it 2.

20.00------------------2
50.00-------------1
-50.00------------1
80.00------------------2
-100.00---------------2

Can that this be done and can it be done in the same cell/Column like your paste in V2

Was that too detail

The only solutions I have seen for this is using Excel's SOLVER function (with which I have no experience). I know SOLVER can be invoked from a macro so this is probably the way to go.

I'll have a search later (going out for a while) to see if I can find a solution.

Using the following it only finds one match (which you had identified):

=IF(SUMIFS(E:E,B:B,B2,V:V,"<>1")=0,2,"")

Are there cases of 1:n where a subset of Debits = subset of Credits?

The below formula is well but it is also pulling numbers that are the same. Meaning if i have2 number in my data 28.50, 28.50 it still pulls that data, beside only pulling the values that are opposite -28.50, 28.50

[code]=IF(SUMPRODUCT(($A$2:$A$995=A2)*(ABS($C$2:$C$995)=ABS(C2)))=2,1,"")[code]