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...

	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​
14​	145​	0​	2000​	0​	0​
15​	145​	1000​	0​	0​	0​
16​	145​	0​	1000​	0​	2000​

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.