sum substract records on the basis of duplicate records

[sanjuss2]

Hi Sir
Thanks for your reply
Please give me solution on the issue i am facing now. actually i have 1 sheet with 5 columns as follows.
------ A ---------- B ----------------C ------------------ D --------- E
1 ---A/c no.---bal 310313-----bal 310314---addition----Release
2-----121 -------1280 ------------ 0 -------------- 1280 ------ 0
3-----123------- 3500------------- 0 -------------- 900-------- 0
4 -----123------- 0 -------------2600 ---------------- 0 --------- 0
5 -----129-------5100------------3900 ------------1200------- 0
6 -----126------- 0 -------------3800 -----------------0---------- 0
7 -----126------- 4800----------- 0 ---------------1000-------- 0
8 -----128------- 0 -------------3800 --------------0----------3800
9-----130--------0----------- 6800----------------0---------- 2600
10----130-----4200------------0-----------------0------------0
11----136------ 1000----------3000---------------0--------- 2000
12----140----- -2450-----------0 -----------------0---------- 0
13----140---------0----------- 6450---------------0---------- 4000

in above data I need to calculate amount as follows
Column D & C are answers
Row no. 2 : having Col B > C & single record so the balance appear in col. D (B-C)
Row 3 & 4 : are duplicate record i.e same a/c no. & col B > C so sum(B –C) appears in ADD i.e. col. D
Row no. 5 : having Col B > C & single record so the balance appear in col. D ADD (B-C)
Row 6 & 7 : are duplicate records i.e same a/c no. & col B > C so sum(B –C) appears in ADD i.e. col. D
Row no. 8 : having Col C > B & single record so the balance appear in col. E i.e. RELEASE (C-B)
Row 9 & 10 : are duplicate records i.e same a/c no. & col C > B so sum(C –B) appears in RELEASE i.e. col. E (C-B)
Row no. 11 : having Col C > B & single record so the balance appear in col. E i.e. RELEASE (C-B)
Row 12 & 13 : are duplicate records i.e same a/c no. & col C > B so sum(C –B) appears in RELEASE i.e. col. E (C-B)

Your formula provide half result, so I hereby requesting you to kindly provide me the solution.
Thanks in advance
awaiting for reply

 

[Marcelo Branco]

Maybe this...


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

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

1​

[/td][td]

A/c no.​

[/td][td]

bal 310313​

[/td][td]

bal 310314​

[/td][td]

addition​

[/td][td]

Release​

[/td][/tr]


[tr][td]

2​

[/td][td]

121​

[/td][td]

1280​

[/td][td]

0​

[/td][td]

1280​

[/td][td]

0​

[/td][/tr]


[tr][td]

3​

[/td][td]

123​

[/td][td]

3500​

[/td][td]

0​

[/td][td]

900​

[/td][td]

0​

[/td][/tr]


[tr][td]

4​

[/td][td]

123​

[/td][td]

0​

[/td][td]

2600​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

5​

[/td][td]

129​

[/td][td]

5100​

[/td][td]

3900​

[/td][td]

1200​

[/td][td]

0​

[/td][/tr]


[tr][td]

6​

[/td][td]

126​

[/td][td]

0​

[/td][td]

3800​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

7​

[/td][td]

126​

[/td][td]

4800​

[/td][td]

0​

[/td][td]

1000​

[/td][td]

0​

[/td][/tr]


[tr][td]

8​

[/td][td]

128​

[/td][td]

0​

[/td][td]

3800​

[/td][td]

0​

[/td][td]

3800​

[/td][/tr]


[tr][td]

9​

[/td][td]

130​

[/td][td]

0​

[/td][td]

6800​

[/td][td]

0​

[/td][td]

2600​

[/td][/tr]


[tr][td]

10​

[/td][td]

130​

[/td][td]

4200​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

11​

[/td][td]

136​

[/td][td]

1000​

[/td][td]

3000​

[/td][td]

0​

[/td][td]

2000​

[/td][/tr]


[tr][td]

12​

[/td][td]

140​

[/td][td]

2450​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

13​

[/td][td]

140​

[/td][td]

0​

[/td][td]

6450​

[/td][td]

0​

[/td][td]

4000​

[/td][/tr]


[tr][td]

14​

[/td][td]

145​

[/td][td]

0​

[/td][td]

2000​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

15​

[/td][td]

145​

[/td][td]

1000​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]


[tr][td]

16​

[/td][td]

145​

[/td][td]

0​

[/td][td]

1000​

[/td][td]

0​

[/td][td]

2000​

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

Formula in D2 copied down
=IF(AND(B2>0,COUNTIFS(A:A,A2,B:B,">0")=COUNTIFS(A$2:A2,A2,B$2:B2,">0")),MAX(0,SUMIF(A:A,A2,B:B)-SUMIF(A:A,A2,C:C)),0)

Formula in E2 copied down
=IF(AND(C2>0,COUNTIFS(A:A,A2,C:C,">0")=COUNTIFS(A$2:A2,A2,C$2:C2,">0")),MAX(0,SUMIF(A:A,A2,C:C)-SUMIF(A:A,A2,B:B)),0)

Hope this helps

M.