Identify numbers that net to zero in column

[Darryproduct]

I'm trying to reconcile accounts that have multiple transactions that net to zero. Often there can be 1-4 numbers in the credit column that equal 1-4 numbers in the debit column. Is there a formula or can I utilize solver to help identify all the numbers that net to 0?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Transaction Date[/TD]
[TD]Journal #[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Difference[/TD]
[/TR]
[TR]
[TD]4/3/19[/TD]
[TD]63564[/TD]
[TD][/TD]
[TD]-127.41[/TD]
[TD]-127.41[/TD]
[/TR]
[TR]
[TD]4/9/19[/TD]
[TD]63803[/TD]
[TD][/TD]
[TD]-984.82[/TD]
[TD]-984.82[/TD]
[/TR]
[TR]
[TD]4/9/19[/TD]
[TD]63804[/TD]
[TD][/TD]
[TD]-3771.17[/TD]
[TD]-3771.17[/TD]
[/TR]
[TR]
[TD]4/9/19[/TD]
[TD]63747[/TD]
[TD]99.50[/TD]
[TD][/TD]
[TD]99.50[/TD]
[/TR]
[TR]
[TD]4/9/19[/TD]
[TD]63747[/TD]
[TD]3933.48[/TD]
[TD][/TD]
[TD]3933.48[/TD]
[/TR]
[TR]
[TD]4/9/19[/TD]
[TD]63747[/TD]
[TD][/TD]
[TD]-314.10[/TD]
[TD]-314.10[/TD]
[/TR]
[TR]
[TD]4/9/19[/TD]
[TD]63747[/TD]
[TD][/TD]
[TD]-104.40[/TD]
[TD]-104.40[/TD]
[/TR]
[TR]
[TD]4/9/19[/TD]
[TD]63747[/TD]
[TD]1,199.42[/TD]
[TD][/TD]
[TD]1,199.42[/TD]
[/TR]
[TR]
[TD]4/29/19[/TD]
[TD]64182[/TD]
[TD]91.20[/TD]
[TD][/TD]
[TD]91.20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SUM[/TD]
[TD]-$36.21[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to identify which numbers are creating the discrepancy of ($36.21) when it should be $0.

 

[GR00007]

How are you determining what values are to sum to zero - by account#, journal#?
The current sample given sums to a difference of 21.70.
Here's a formula to sum by a value: Journal #
[TABLE="class: grid, width: 343"]
<tbody>[TR]
[TD]Transaction Date[/TD]
[TD]Journal #[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Journal Sum[/TD]
[/TR]
[TR]
[TD="align: right"]4/3/2019[/TD]
[TD="align: right"]63564[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-127.41[/TD]
[TD="align: right"]-127.41[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63803[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-984.82[/TD]
[TD="align: right"]-984.82[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63804[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-3771.2[/TD]
[TD="align: right"]-3771.17[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]99.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]3933.48[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-314.1[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-104.4[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]1,199.42[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/29/2019[/TD]
[TD="align: right"]64182[/TD]
[TD="align: right"]91.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]91.2[/TD]
[/TR]
</tbody>[/TABLE]
The formula in column E is:

=SUMIFS(C$2:C$10,B$2:B$10,B10)+SUMIFS(D$2:D$10,B$2:B$10,B10)

 

[Darryproduct]

How are you determining what values are to sum to zero - by account#, journal#?
The current sample given sums to a difference of 21.70.
Here's a formula to sum by a value: Journal #
[TABLE="class: grid, width: 343"]
<tbody>[TR]
[TD]Transaction Date[/TD]
[TD]Journal #[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Journal Sum[/TD]
[/TR]
[TR]
[TD="align: right"]4/3/2019[/TD]
[TD="align: right"]63564[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-127.41[/TD]
[TD="align: right"]-127.41[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63803[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-984.82[/TD]
[TD="align: right"]-984.82[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63804[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-3771.2[/TD]
[TD="align: right"]-3771.17[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]99.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]3933.48[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-314.1[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-104.4[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/9/2019[/TD]
[TD="align: right"]63747[/TD]
[TD="align: right"]1,199.42[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4813.9[/TD]
[/TR]
[TR]
[TD="align: right"]4/29/2019[/TD]
[TD="align: right"]64182[/TD]
[TD="align: right"]91.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]91.2[/TD]
[/TR]
</tbody>[/TABLE]
The formula in column E is:

=SUMIFS(C$2:C$10,B$2:B$10,B10)+SUMIFS(D$2:D$10,B$2:B$10,B10)

Sorry I wasn't specific enough. The "Difference" column is the sum of C and D. All credits are shown with a negative balance while a debit is a positive. The sum of the "Difference" column is -$36.21. I'm trying to identify which rows net to 0 and which rows net to the -$36.21 Difference.

I tried the formula but it doesn't seem to do this...if it does maybe I'm missing something.

 

[philwojo]

The Sum of your Difference column is not -$36.21, but rather it is $21.67 (positive). So you have more debits than credits.

How did you get to the -$36.21 value, I'm not following that part.
If you sum up the Debit column I get $5323.60
If you sum up the Creidts column I get -$5310.93, that is a difference of $21.67.

Also, to GR00007's point how are you trying to match these up. None of the debits exactly matches any of the Credits, so there is no one-to-one ratio to try and find out which one's don't match, at least not without more information to give us some sort of correlation.

 

[GR00007]

Note: 91.2 + (-127.41) does equal $-36.21, but how in the world are the first and last rows of the sample data related?