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.

 

[EmilXmulo]

One thing I’d be interested to know is whether the received quantity table can contain multiple receipt entries for the same PO# as well. If so, does the same formula still work when several partial receipts are entered on different dates?

 

[shamimarita]

Hello EmilXmulo,

Yes. The received quantity table can contain multiple entries for the same PO#, including partial receipts entered on different dates. The same formula will still work for calculating the current Outstanding Qty.

This part of the formula: SUMIF($G:$G,A9,$J:$J)
adds all received quantities in column J that have the matching PO# in column G. So, for example, if PO# 52698 has three receipt entries of 40, 30, and 33 units, Excel will treat the total received quantity as 103 and allocate it sequentially against the ordered rows.

The receipt dates do not affect the current formula; it simply uses the total received quantity for that PO#. If you need to calculate the Outstanding Qty as of a particular receipt date, then the formula would need to be modified to include the date as an additional criterion, for example using SUMIFS.

Hope this clarifies it!

 

[garfieldandew]

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.

View attachment 1862

Thank you very much.

PO# alone isn't enough because PO 52698 appears on three separate rows. You need the received quantity to be applied to those rows in order, instead of giving the same received amount to every matching PO.


If you want the received quantities to reduce the first matching ordered row first, then move to the next row, use this in D8 and drag down:

=MAX(0,C8-MAX(0,SUMIFS($J$8:$J$100,$G$8:$G$100,A8)-SUMIFS($C$8:C7,$A$8:A7,A8)))

For your example, if PO 52698 has ordered quantities of 103, 105, 103, and the received table eventually contains:

• 52698 → 103
• 52698 → 105