[SOLVED] Automatic subtraction according to conditions in Google Sheets

Automatic subtraction according to conditions in Google Sheets

how to make automatic subtraction vertically downwards according to conditions in Google Sheets

SKU	SKU BALANCE	ID CUST	CUST ORDER	DESIRE FORMULA
10100	100	115501	90	10
10100	100	115502	10	0

10036	100	115503	25	75
10036	100	115504	25	50
10036	100	115505	5	45

10040	100	115506	50	50
10040	100	115507	25	25
10040	100	115508	30	-5

10041	100	115519	50	DUPLICATES
10041	100	115519	50	DUPLICATES
10041	100	115519	50	DUPLICATES

10036	100	115520	45	0

https://docs.google.com/spreadsheets/d/1lJKWrmMXNtvNoVY7KiBSX-_b7yKPh6Ooa3gcnubqi3o/edit?gid=0#gid=0

Solution

Here's one generalized formula approach which you may adapt accordingly

Also the formula as of now does not deal with a scenario where lets say SKU 10041 has only 2 dupe ID CUST no.s and one distinct no. or so since no clarity is given on such a thing!

=map(A2:A,lambda(Σ,if(Σ="",,if(let(Λ,filter(C:C,A:A=Σ),counta(Λ)<>countunique(Λ)),"DUPLICATE",+filter(B:B,A:A=Σ)-sum(chooserows(filter(D:D,A:A=Σ),sequence(countif(A2:Σ,Σ))))))))