Need to Identify Pending qty against Open po qty


Hi ,

Hope some can help me

I have set of PO qty which is raised daily, and for the same I will receiving qty partially against each PO's

The challenge I'm facing is to identify the remaining PO qty of a particular PO, ( As the Materials are received partially sometimes receipt will done once in 3 months.

Note : Receiving will not in sequence as there will be multiple materials received against multiple PO's

Sample calculation PO PO qty PO Receiving qty Receiving date Pending PO qty 12345 100000 12345 20000 18-07-2016 80000 56789 50000 12345 10000 20-07-2016 70000     56789 20000 20-07-2016 30000     12345 50000 20-07-2016 Here we want a formula which auotmatically calculate the remaining qty of PO# 12345, when ever this is entry against the PO




Try this in cell 'Receiving Qty'!I2.

=SUMIF('PO details'!$B:$B,'Receving qty'!$B2,'PO details'!$E:$E)-SUMIF($B:$B,$B2,$E:$E)



Thanks for your anser it's working.

But some times on the same PO we will be having multiple Items and multiple qty, using this formula it's calculating on PO itself, please help on this

I have udpated a new file as well.
manuprasad29 Jul 22, '16 at 3:58 am
OK, this should get you a PO / Item specific qty.

=SUMIFS('PO details'!$F:$F,'PO details'!$C:$C,'Receving qty'!$B2,'PO details'!$E:$E,'Receving qty'!$D2)-SUMIFS($E:$E,$B:$B,$B2,$D:$D,$D2)
MAQ (rep: 5) Jul 22, '16 at 8:51 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login