Sum if and Equal To

[GCLIFTON]

Need help with the formula below.
How can i make it also say that if the number are 85.00 and -85.00 say zero as well. This formula is only giving me the total sum of more than one number add together. How can i have it to say Zero if it is a 1 for 1 match the equal to zero

Please Login or Register  to view this content.

[avk]

if your data in "A2" is 85 & "b2" is -85
in "C2" formula

Please Login or Register  to view this content.

[GCLIFTON]

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

[FDibbins]

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[GCLIFTON]

Here is the data

[AliGW]

I cannot see the formula quoted in post #1 in the attached spreadsheet - where should it appear?

[GCLIFTON]

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

[AliGW]

There is still no attempted formula in your workbook, so you have not answered my question.

[GCLIFTON]

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

[AliGW]

Sorry - I'm not getting this. I'll see if I can drum up some help.

[GCLIFTON]

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

[FDibbins]

This is your formula using a regular range...
=SUMIFS(G:G,C:C,C2)

A quick scan (and a quick calc to test), shows that all your amounts are to no more than 2 decimals anyway, so the ROUND is redundant
If you have 85 and -85 on the same operation, and add them together, they will give 0 anyway, so I really dont understand what you are trying to do here?

[GCLIFTON]

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"

[GCLIFTON]

any suggestions

[JohnTopley]

If I understand correctly for a given operation (40780 is a good example) there are 6 single matches so we need take a given value and check if there a 1:1 match,

Having eliminated these, we then look at the remainder to see there is a (one or more) 1: n match: the latter is not straight forward (if not impossible without many iterations!) to do as we have no idea what the target sum is.

Try this for the 1:1 matches

in V2


=IF(SUMPRODUCT(($C$2:$C$1000=C2)*(ABS($G$2:$G$1000)=ABS(G2)))=2,1,"")

copy down

[GCLIFTON]

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

[GCLIFTON]

Was that too detail

[JohnTopley]

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.

[JohnTopley]

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?

[GCLIFTON]

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]