Netting -Non Netting with condition

[Ebraham]

Hi All,

Below in the column b i have put the formula as =+COUNTIF($A$2:A2,A2)>COUNTIF($A$2:$A$13,-A2) which gives me false if there is an netting available for that amount in that column A. But i want if the column C also has the same number or string along with the netting(False-ColumnA) found and non netting criteria. The Column D is what i want with excel formula.

[TABLE="width: 333"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Desired results[/TD]
[/TR]
[TR]
[TD]      (17,818,186.05)[/TD]
[TD]FALSE[/TD]
[TD]1[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]        17,818,186.05[/TD]
[TD]FALSE[/TD]
[TD]1[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]         (6,255,054.34)[/TD]
[TD]FALSE[/TD]
[TD]-2[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]           6,255,054.34[/TD]
[TD]FALSE[/TD]
[TD]1[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]           6,255,054.34[/TD]
[TD]TRUE[/TD]
[TD]-2[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]           6,138,137.31[/TD]
[TD]TRUE[/TD]
[TD]5[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]         (6,137,066.43)[/TD]
[TD]FALSE[/TD]
[TD]0[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]           6,137,066.43[/TD]
[TD]FALSE[/TD]
[TD]0[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]   (108,715,352.81)[/TD]
[TD]FALSE[/TD]
[TD]cdgf[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]     108,715,352.81[/TD]
[TD]FALSE[/TD]
[TD]cdgf[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]                     5,454.00[/TD]
[TD]TRUE[/TD]
[TD]5[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]                     4,545.00[/TD]
[TD]TRUE[/TD]
[TD]2[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

But i want if the column C also has the same number or string along with the netting(False-ColumnA) found and non netting criteria.

???

 

[Ebraham]

???

For the first two row if you see the amount is also netting and the numbers coreponding to this netting in column c is also same that is 1 for both, so it gave true.
Whereas for cell A3 and cell A5 give true bcoz of -2 corresponding to this netting matches.
The cells A3 and A4 did not give true bcoz the corresponding does not match in col c.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Column A​

[/td][td="bgcolor:#F3F3F3"]

Column B​

[/td][td="bgcolor:#F3F3F3"]

Column C​

[/td][td="bgcolor:#F3F3F3"]

Desired results​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

(17,818,186.05)​

[/td][td="bgcolor:#CCFFCC"]

TRUE​

[/td][td]

1​

[/td][td]

TRUE​

[/td][td="bgcolor:#CCFFCC"]B2: =COUNTIFS($A$2:$A$13, A2, $C$2:$C$13, C2) = COUNTIFS($A$2:$A$13, -A2, $C$2:$C$13, C2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

17,818,186.05​

[/td][td="bgcolor:#CCFFCC"]

TRUE​

[/td][td]

1​

[/td][td]

TRUE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

(6,255,054.34)​

[/td][td="bgcolor:#CCFFCC"]

TRUE​

[/td][td]

-2​

[/td][td]

TRUE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

6,255,054.34​

[/td][td="bgcolor:#CCFFCC"]

FALSE​

[/td][td]

1​

[/td][td]

FALSE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

6,255,054.34​

[/td][td="bgcolor:#CCFFCC"]

TRUE​

[/td][td]

-2​

[/td][td]

TRUE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

6,138,137.31​

[/td][td="bgcolor:#CCFFCC"]

FALSE​

[/td][td]

5​

[/td][td]

FALSE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

(6,137,066.43)​

[/td][td="bgcolor:#CCFFCC"]

TRUE​

[/td][td]

0​

[/td][td]

TRUE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

6,137,066.43​

[/td][td="bgcolor:#CCFFCC"]

TRUE​

[/td][td]

0​

[/td][td]

TRUE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

(108,715,352.81)​

[/td][td="bgcolor:#CCFFCC"]

TRUE​

[/td][td]cdgf[/td][td]

TRUE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

108,715,352.81​

[/td][td="bgcolor:#CCFFCC"]

TRUE​

[/td][td]cdgf[/td][td]

TRUE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

5,454.00​

[/td][td="bgcolor:#CCFFCC"]

FALSE​

[/td][td]

5​

[/td][td]

FALSE​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

4,545.00​

[/td][td="bgcolor:#CCFFCC"]

FALSE​

[/td][td]

2​

[/td][td]

FALSE​

[/td][td][/td][/tr]
[/table]

 

[Ebraham]

Worked as a treat. Thanks.

 

[shg]

You're welcome.

 

[Ebraham]

if i change the cell C5 to -2 the formula does not work. It should give true to one of the pair which has the corresponding matches. i did not encountered the condition before so i did not included that condition before. Can you kindly advise ?

C4 = True
C5= True
C6 = False

 

[Ebraham]

For your reference.

[TABLE="width: 364"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Your Formula[/TD]
[TD]Column C[/TD]
[TD]Desired[/TD]
[/TR]
[TR]
[TD]-17,818,186.05[/TD]
[TD="align: center"]TRUE[/TD]
[TD]1[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]17,818,186.05[/TD]
[TD="align: center"]TRUE[/TD]
[TD]1[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]-6,255,054.34[/TD]
[TD="align: center"]FALSE[/TD]
[TD]-2[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]6,255,054.34[/TD]
[TD="align: center"]FALSE[/TD]
[TD]-2[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]6,255,054.34[/TD]
[TD="align: center"]FALSE[/TD]
[TD]-2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]6,138,137.31[/TD]
[TD="align: center"]FALSE[/TD]
[TD]5[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]-6,137,066.43[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]6,137,066.43[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]-108,715,352.81[/TD]
[TD="align: center"]TRUE[/TD]
[TD]cdgf[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]108,715,352.81[/TD]
[TD="align: center"]TRUE[/TD]
[TD]cdgf[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]5,454.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]5[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]4,545.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]-6,255,054.34[/TD]
[TD="align: center"]FALSE[/TD]
[TD]765[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]