@Danilang and @DavidP, thanks a lot!
The logic of the last formula may not seem intuitive. I would definitely love to see other approach that would be more straightforward to get the results.
I will try to divide the formula into different parts. You may also use the "Evaluate Formula" Function in excel to simplify the formulas.
Let's take the formula in cell M4 as an example:
=SUMPRODUCT(--(-SUM($L$3:L4)>I$3:I4),-SUM($L$3:L4)-$I$3:I4,K$3:K4)-SUM($M$3:M3)
1st part: --(-SUM($L$3:L4)>I$3:I4):
= {If sum(L3:L4) > I3, then = 1, else 0 ; If sum(L3:L4) >I4, then = 1, else 0}
The results of the first part would be {1;0}
2nd part: -SUM($L$3:L4)-$I$3:I4,K$3:K4
= {-SUM(L3:L4) - I3 ; SUM(L3:L4) - I4}, {K3 ; K4}
= {100-0 ;100-100}, {K3 ; K4},
= {100;0},{K3;K4}
= {100;0}, {8;0}
3rd part: -SUM($M$3:M3)
= 0
So, adding all the parts will be equal to SUMPRODUCT( {1;0}, {100;0), {8;0} ) - 0
=( 1*100*8 + 0*0*0 ) - 0
= 800
Using the same logic, the formula in cell M9 =SUMPRODUCT(--(-SUM($L$6:L9)>I$6:I9),-SUM($L$6:L9)-$I$6:I9,K$6:K9)-SUM($M$6:M8)
= SUMPRODUCT ( {1;1;1;0} , {150;50;50;0}, {150;0;10;0} ) - SUM(M6:M8)
= ( 1*150*150 + 1*50*0 + 1*50*10 + 0*0*0 ) - 15000
= 8000
The logic is to calculate the cumulative total cost of sales starting from the beginning by SUMPRODUCT(--(-SUM($L$6:L9)>I$6:I9),-SUM($L$6:L9)-$I$6:I9,K$6:K9) and then subtract the previous cost of sales up to last transaction with - SUM($M$6:M8). So, the remaining balance would be the cost of sales for that particular transaction. Hope that is clearer.
