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.

 

[Richard03]

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.

This makes sense to me, and I like the sequential allocation approach. I just want to clarify one point: does this formula assume that the received quantity should always be applied to the matching PO rows from top to bottom?

For example, if PO# 52698 has three ordered rows of 103, 105, and 103, and I receive 103 units, the formula would make the first row’s Outstanding Qty 0 and leave 105 and 103 outstanding on the next two rows. That seems correct if the receipts are meant to be allocated in that order.

 

[wiferetire]

For example, if your first data row is row 9, enter this formula in D9 and copy it down:
=MAX(0,C9-MAX(0,SUMIFCAR GAMES($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.

I really like the way you explain, easy to understand, shamimarita. If the total received quantity is greater than the total ordered quantity for a duplicate PO#, will this formula automatically prevent the extra received quantity from creating a negative Outstanding Qty?

 

[shamimarita]

This makes sense to me, and I like the sequential allocation approach. I just want to clarify one point: does this formula assume that the received quantity should always be applied to the matching PO rows from top to bottom?

For example, if PO# 52698 has three ordered rows of 103, 105, and 103, and I receive 103 units, the formula would make the first row’s Outstanding Qty 0 and leave 105 and 103 outstanding on the next two rows. That seems correct if the receipts are meant to be allocated in that order.

Hello Richard03,

Yes, exactly. The formula assumes that the received quantity is allocated to duplicate PO# rows from top to bottom, based on their order in the table.

So, for ordered quantities of 103, 105, and 103, if 103 units are received, the first row will be fully covered and show an Outstanding Qty of 0, while the next two rows will remain 105 and 103.

If the receipts need to be assigned in a different order, such as by date, item, or another priority, the formula would need to be adjusted accordingly.

 

[shamimarita]

I really like the way you explain, easy to understand, shamimarita. If the total received quantity is greater than the total ordered quantity for a duplicate PO#, will this formula automatically prevent the extra received quantity from creating a negative Outstanding Qty?

Hello wiferetire,

Thanks for your kind feedback! Yes, the formula will automatically prevent the Outstanding Qty from becoming negative even when the total received quantity is greater than the total ordered quantity for the same PO#.

The outer MAX(0,...) ensures that the minimum Outstanding Qty displayed is 0. Therefore, any excess received quantity will not produce a negative value in column D.

For example, if the total ordered quantity is 300 and the total received quantity is 350, all applicable Outstanding Qty cells will simply become 0.