Hi Saliha!

I came up with a SUMIFS nested inside an IF; it was fun! You will need to allow for growth, or, better yet, make it a table so the formula will automatically grow with the tables. Hopefully, this helps you! 

Hi Jessica Stewart

Thanks for your help but there is one more step is missing delivery date, bcoz we need to update qty date wise with S.L.

1. one more i noticed if i remove i10 arrived qty 225 result displaying in k3 arrived qty is -150 in negative,
2. actually i12 qty (100) should be update in k3 cell and balance qty will be 150
3. and k6 arrived qty will be zero.

Saliha

I would recommend to change the set-up a bit. Have one table with three columns for the quantities (OrderedQTY, ReceivedQTY and AllocatedQTY). Then you can fairly easily calculate the allocation of received quantities. Note that I didn't consider the possibility that you would receive more than ordered, but that shouldn't be all that difficult either.

Please see if the attached solution could work for you.

I see what you did there, Riny; much more simple and cleaner! I like it.

Hi Riny / Jessica

Thanks for you help, i have achieve 80% of my work based on your allocated formula option, possible to add delivery date wise, which i was message on first.

qty should be update first delivery date wise

I need to update Arrived qty (c10:c12) in (e3:e6) based on S.L (b column) and delivery date (c3:c6), there is possible to auto update formula.

regards/Saliha

Hi Riny

i have updated your formula working file but i need qty updation based on first of date condition

Saliha

Not sure I follow and you didn't put all transactions in one table with separate columns for Qty Ordered and Qty Received. Then you can sort all transactions by date in ascending order, and apply the formula I used in my previous post.