Formula to determine which values Contra each Other

[Howardc1001]

I have a formula to contra Debit and Credit Values in Col, H by returning a true of false (these contra entries will sum to zero)

However, if there are 2 debit amounts for e.g. 100 each and one credit for -100, then the formula will return "True" for all 3

I need the formula to be amended to return "True" only for items that Contra out in Col H i.e match the debit or credit that appears first and then match the rest


I have highlighted the item in h4 that returns 'true" i.e., but should be "False" as it has occurred a gain but has no contra entry i.e. a credit for the same amount that will sum it to zero

my formula is

Please Login or Register  to view this content.

it would be appreciated if someone could kindly amend my formula

[JohnTopley]

This ??

=IF(COUNTIF($H$2:H2,H2)=1,SUMPRODUCT(--(ABS($H$2:$H$17)=ABS(H2)))>1,FALSE)

[Howardc1001]

Thanks very much for the help. Your formula works 100%