Multiple line subtotal

[mijofr]

Half the problem is phrasing my question.

I have two separate spreadsheets.

Book 1, sheet 1, lists production by lot number.
 Column “A” lists the lot number, column “L” lists the quantity produced.
Book 2, sheet 1, lists shipments by lot number.
 Column “F” lists the lot number, column “G” lists the quantity shipped.

I’m developing book 3, sheet 1, which will compare shipments against production, and show me remaining inventory by lot number.
 Column “A” lists the lot number, column “J” lists the total quantity shipped (or is supposed to)
I have the following formula in cell “J2” which looks to book 2 and retrieves the quantity shipped from column “G”.
 =VLOOKUP(A2,'[book2]sheet1'!$F:$G,2,FALSE)

Here is my problem. The formula listed only retrieves a single shipment. There are times when a production lot is shipped on multiple lines. I need a formula that will retrieve and subtotal all of the shipments from that lot number.

I’m lost and need a push in the right direction.

Thanks all.