Calculation based on lookup values

[snowbunny99]

Hi everyone,

Could you please help me with a formula to calculate the Outstanding Qty in column D?
There are 2 tables: the left one is to track the ordered qty and outstanding qty; the right one is to track the received qty.
I would like to calculate the qty in column D using the values in columns C and J, when the values in columns A and G match.
I added a formula to match the PO# in 2 tables and perform the calculations.
But I am having trouble calculating the outstanding qty for rows with the same PO#.
For example, when I enter the PO#52698 in G11, the values in D10 changes 103 and the same calculation applies to all 3 cells D9, D10, D11.

I need some way to still track the PO in different rows and calculate the remaining qty of each row.



Thank you very much.

 

[shamimarita]

Hi snowbunny99,

You can handle the duplicate PO# rows by allocating the received quantity to each ordered row sequentially instead of applying the full received quantity to every matching row.

For example, if your first data row is row 9, enter this formula in D9 and copy it down:
=MAX(0,C9-MAX(0,SUMIF($G:$G,A9,$J:$J)-SUMIF($A$8:A8,A9,$C$8:C8)))

How it works:

• SUMIF($G:$G,A9,$J:$J) returns the total quantity received for the PO# in A9.
• SUMIF($A$8:A8,A9,$C$8:C8) calculates how much was ordered in the previous rows for the same PO#.
• The remaining received quantity is then applied to the current row.
• MAX(0,...) prevents the Outstanding Qty from becoming negative.

So, if PO# 52698 appears in several rows, Excel will first use the received quantity against the first occurrence, then apply any remaining quantity to the next occurrence, and so on. This allows each row to show its own correct Outstanding Qty instead of displaying the same calculation for all matching PO# rows.

Please try the formula and let us know if it gives the expected result.