VLOOKUP Array to Sum Multiple Instances

[paulrh]

I have a simple warehouse stock workbook. One sheet has the parts inventory, a second sheet has the orders. The second sheet (called ORDERS) has orders fulfilled such that the part number is in the first column and the number ordered for that part in the fourth column.

I want the inventory sheet to sum up all orders of a particular part and subtract that from the inventory total.

This is the formula I have used on the inventory sheet (using row 32 as an example)

{=SUM(D32:F32) - IFNA(SUM(VLOOKUP(A32,ORDERS!$C$4:$F$20000,{4},FALSE)),0))

As you can see I have entered it as an array.

The IFNA removes the #N/A error if the part has never been ordered.

The SUM(D32:F32) is the incoming numbers for that part.

This formula appears to stop at the first instance of the part being encountered. I searched and see I am not the first person to have this issue and the formula I have used appears to match peoples suggested solutions but it still does not work.

Any ideas? Thanks

 

[paulrh]

Solved it!

=SUM(D2:F2) - SUMPRODUCT(SUMIF(INDIRECT("'"&ORDERS2&"'!"&"$C$4:$C$2000"),A2,INDIRECT("'"&ORDERS2&"'!"&"$F4:$F$20000")))

 

[Marcelo Branco]

Not sure i understand your data setup.
See if this does what you need
=SUM(D32:F32) - SUMIF(ORDERS!$C:$C,A32,ORDERS!$F:$F)

M.

 

[Marcelo Branco]

Solved it!

=SUM(D2:F2) - SUMPRODUCT(SUMIF(INDIRECT("'"&ORDERS2&"'!"&"$C$4:$C$2000"),A2,INDIRECT("'"&ORDERS2&"'!"&"$F4:$F$20000")))

No need of SUMPRODUCT and INDIRECT. See my formula above.

M.