Problem figuring out a formula using VLOOKUP?

Hello,

I am trying to automate some order forms. Product Sheet is a list of products and quantities. Product-Parts Sheet shows which parts and quantities are needed for each product. I would like for the Parts Order Sheet to calculate the number of each part I need to order based on the product quantity and part quantity per product. Products have multiple parts and parts can be in multiple products.

What I need my formula to return is the following:
(quantity of p1 for product A) * (quantity of Product A) +
(quantity of p1 for product B) * (quantity of Product B) +
(quantity of p1 for product C) * (quantity of Product C) +
(quantity of p1 for product D) * (quantity of Product D) +
and so on for all values matching (p1)

It seems like it might be a nested VLOOKUP function?


Product Sheet
Product Quantity
A 2
B 5
C 3
D 8

Product-Parts Sheet
Product Part Quantity
A p1 1
A p2 2
A p3 1
B p1 2
B p3 1
C p3 1
C p4 3
C p5 2
D p2 1

Parts Order Sheet
part part quantity
p1 12
p2 12
p3 10
p4 9
p5 6

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

The easiest-to-maintain solution involves a helper column:

E11, copied down: =SUMPRODUCT(($A$3:$A$6=A11)*$B$3:$B$6)*C11

and E24, copied down: =SUMIF($B$11:$B$19,A24,$E$11:$E$19)