How to find balance stock cost of SKU based on received date?-CodePudding

Above is an example of data in Google Sheets. The green table is to keep track my purchases and the red table is to show the inventory summary.

I am stuck at creating a formula to calculate the Balance Stock Cost.

The Balance Stock Cost should only sum up the cost of unsold quantity from latest received date.

For example, for SKU A, the stock balance is 31 units. The purchases of SKU A sorted by received date will become like this...

Since the stock balance is 31, the formula should sum up the purchases on 10-Jan (5 units x 6), 7-Jan (25 units x 4) and 5-Jan (1 unit x 5.5) which brings the total to 135.5.

Please help me to come up a formula to calculate the Balance Stock Cost based on above requirements. Thanks.

CodePudding user response：

You can try:

=MAP(A18:A,D18:D,LAMBDA(ax,dx,IF(ax="",,SUM(QUERY(SORT(ARRAYFORMULA(split(flatten(A2:A15&"|"&B2:B15&"|"&D2:D15&"|"&MAP(C2:C15,LAMBDA(cx,TRANSPOSE(SEQUENCE(cx,1,1,0))))),"|",0,0)),1,1,2,0),"Select Col3 Where Col4 is NOT NULL AND Col1='"&ax&"' LIMIT "&dx)))))