Return a sum values as based on task ID and Ledger by ignoring duplicates

[mmr1]

Hi,

Required a formula help to as per data available in working sheet, return a sum values in column C as based on task ID and Ledger by ignoring duplicates whatever available in column A and B (task ID and Ledger) in the summary sheet.

Expected results are available in summary sheet column E.


Thanks,

return the sum values by ignoring duplicate criteria.04.10.21.xlsx
	A	B	C	D	E
2	Task ID	Ledger	Total Value		Expected Results
3	1000	52000200			56,053.89
4	1000	52000200			-
5	1000	52000201			72,881.56
6	1010	52000200			340,465.94
7	1010	52000201			94,959.22
8	1010	52000201			-
9	1020	52000200			125,245.28
10	1020	52000201			139,573.04
11	1100	52000200			6,572.36
12	1100	52000201			-
13	1101	52000200			-
14	1101	52000200			-
15	1101	52000201			4,848.00
16	1101B	52000200			-
17	1101B	52000201			-
18	1102	52000200			-
19	1102	52000201			-
20	1102	52000201			-
21	1103	52000200			62,566.79
22	1103	52000201			8,297.90
23	1200	52000200			13,083.58
24	1200	52000201			10,618.30
Summary

return the sum values by ignoring duplicate criteria.04.10.21.xlsx
	A	B	C	D	E	F
1		Internal			External
2	Task ID	Ledger	Total Value	Task ID	Ledger	Total Value
3	1000	52000201	72,881.56	1000	52000200	56,053.89
4	1010	52000201	94,959.22	1010	52000200	340,465.94
5	1020	52000201	139,573.04	1020	52000200	125,245.28
6	1100	52000201	-	1100	52000200	6,572.36
7	1101	52000201	4,848.00	1101	52000200	-
8	1101B	52000201	-	1101B	52000200	-
9	1102	52000201	-	1102	52000200	-
10	1103	52000201	8,297.90	1103	52000200	62,566.79
11	1200	52000201	10,618.30	1200	52000200	13,083.58
Working Sheet

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	Task ID	Ledger	Total Value
2	1000	52000200	56053.89
3	1000	52000200	0
4	1000	52000201	72881.56
5	1010	52000200	340465.9
6	1010	52000201	94959.22
7	1010	52000201	0
8	1020	52000200	125245.3
9	1020	52000201	139573
10	1100	52000200	6572.363
11	1100	52000201	0
12	1101	52000200	0
13	1101	52000200	0
14	1101	52000201	4848
15	1101B	52000200	0
16	1101B	52000201	0
17	1102	52000200	0
18	1102	52000201	0
19	1102	52000201	0
20	1103	52000200	62566.79
21	1103	52000201	8297.9
22	1200	52000200	13083.58
23	1200	52000201	10618.3
Master

Cell Formulas
Range	Formula
C2:C23	C2	=IF(AND(A2=A1,B2=B1),0,IF(--RIGHT(B2),SUMIFS('Working sheet'!$C$3:$C$11,'Working sheet'!$A$3:$A$11,A2,'Working sheet'!$B$3:$B$11,B2),SUMIFS('Working sheet'!$F$3:$F$11,'Working sheet'!$D$3:$D$11,A2,'Working sheet'!$E$3:$E$11,B2)))

 

[mmr1]

Thanks fluff for your solution,

I have an one issue when i insert a new row and copy paste a row like row number 5 (C5) values to row number 8 (C8) it will return a same results instead of blank in C8.

In my original sheet it may be repeated a same codes in many times, i want values to be fixed in only one time when it appeared first rest of all when it repeated the same to be return as a blank.

highlighted one in sample sheet is the value already existed in C5.

return the sum values by ignoring duplicate criteria.04.10.21.xlsx
	A	B	C
1	Task ID	Ledger	Total Value
2	1000	52000200	56,053.89
3	1000	52000200	-
4	1000	52000201	72,881.56
5	1010	52000200	340,465.94
6	1010	52000201	94,959.22
7	1010	52000201	-
8	1010	52000200	340,465.94
9	1020	52000200	125,245.28
10	1020	52000201	139,573.04
11	1100	52000200	6,572.36
12	1100	52000201	-
13	1101	52000200	-
14	1101	52000200	-
15	1101	52000201	4,848.00
16	1101B	52000200	-
17	1101B	52000201	-
18	1102	52000200	-
19	1102	52000201	-
20	1102	52000201	-
21	1103	52000200	62,566.79
22	1103	52000201	8,297.90
23	1200	52000200	13,083.58
24	1200	52000201	10,618.30
Summary

Cell Formulas
Range	Formula
C2:C24	C2	=IF(AND(A2=A1,B2=B1),0,IF(--RIGHT(B2),SUMIFS('Working Sheet'!$C$3:$C$11,'Working Sheet'!$A$3:$A$11,A2,'Working Sheet'!$B$3:$B$11,B2),SUMIFS('Working Sheet'!$F$3:$F$11,'Working Sheet'!$D$3:$D$11,A2,'Working Sheet'!$E$3:$E$11,B2)))

 

[Fluff]

Ok, how about

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,IF(--RIGHT(B2),SUMIFS('Working sheet'!$C$3:$C$11,'Working sheet'!$A$3:$A$11,A2,'Working sheet'!$B$3:$B$11,B2),SUMIFS('Working sheet'!$F$3:$F$11,'Working sheet'!$D$3:$D$11,A2,'Working sheet'!$E$3:$E$11,B2)),0)

 

[mmr1]

Thanks for the response,

somewhere it returns in blank cells FALSE where task id ledger repeated continuously 3 to 4 times, could you please advise to this.

 

[Fluff]

Can you post some sample data showing that problem.

 

[mmr1]

Sorry for late response

here is the sample data for your reference.

Book3
	A			D																								AB
6	Task Code			Ledger Codes																								Sep-21
7
8	1000			52000201																								6,563.00
9	1000			52000200																								1,421.00
10	1010			52000200																								5,654.00
11	1010			52000201																								2,536.00
12	1010			52000200																								FALSE
13	1010			52000201																								FALSE
14	1020			52000200																								65,654.00
32	1510			52000200																								36,584.00
33	1510			52000201																								4,641.60
34	1600			52000200																								37,063.45
35	1600			52000201																								-
36	1600			52000200																								FALSE
37	1600			52000201																								FALSE
38	1600			52000200																								FALSE
39	1600			52000201																								FALSE
40	1600			52000200																								FALSE
41	1600			52000201																								FALSE
Summary

Cell Formulas
Range	Formula
AB8:AB14,AB32:AB41	AB8	=IF(COUNTIFS(A$8:A8,A8,D$8:D8,D8)=1,IF(--RIGHT($D8),SUMIFS('Working Sheet'!$G$5:$G$31,'Working Sheet'!$D$5:$D$31,Summary!$D8,'Working Sheet'!$A$5:$A$31,Summary!$A8),SUMIFS('Working Sheet'!$M$5:$M$31,'Working Sheet'!$J$5:$J$31,Summary!$D8,'Working Sheet'!$A$5:$A$31,Summary!$A8)))

Book3
	A			D			G		I	J			M
5	1000			52000201			6,563.00		1000	52000200			1,421.00
6	1010			52000201			2,536.00		1010	52000200			5,654.00
7	1020			52000201			58,569.00		1020	52000200			65,654.00
16	1500			52000201			14,315.40		1500	52000200			11,819.00
17	1510			52000201			4,641.60		1510	52000200			36,584.00
18	1600			52000201			-		1600	52000200			37,063.45
Working Sheet

 

[Fluff]

Thanks for that, you are missing the ,0 from the end of the formula, it should be

=IF(COUNTIFS(A$8:A8,A8,D$8:D8,D8)=1,IF(--RIGHT($D8),SUMIFS('Working sheet'!$G$5:$G$31,'Working sheet'!$D$5:$D$31,$D8,'Working sheet'!$A$5:$A$31,$A8),SUMIFS('Working sheet'!$M$5:$M$31,'Working sheet'!$J$5:$J$31,$D8,'Working sheet'!$A$5:$A$31,$A8)),0)

Also you should not refer to the sheet name the formula is on as it can cause problems.

 

[mmr1]

Solved,

Many Thanks Fluff for your formula solution.

 

[Fluff]

Glad to help & thanks for the feedback.