Find the product based on multiple criteria

[jvcalandra]

Hello, I am trying to create a database/cut list in Excel and I'm stuck on a particular function. I've added a sample database for reference.

I have (3) worksheets creates so far:
- Summary Sheet, which I want to summarize the data
- Releases Sheet, which is a line-item list of all orders we need to fulfill
- Item Info Sheet, which is a database of items including, part no., description & required hardware items

You'll see that each order that's entered on the 'Releases' sheet has a 'Release No.' associated with it. If you now go to the 'Summary' sheet, you'll see that I've created a table where you can input a release number, and it will summarize the total number of cabinets & the dollar-amount total for that particular release number (input cells are not filled, output cells are filled in with yellow). The input section of that table has space for 4 entries, because there are some instances where there are two separate orders that are shipping to the same destination (in this example, release 8 & 18 are shipping to the same destination, so their data values for cabinet quantity and dollar amount are added together. I've entered each order number on a separate line item below to show the split between the two orders).

For the purposes of this sample, I want to calculate how many Hinges are required for the orders that are entered in the 'Summary' worksheet. If you look at the 'Item Info' sheet, you'll see that Item no. 1299B10012 requires 1 pair of Hinges, Item no. 1299B30039 requires 2 pairs of Hinges, etc. The quantity of Hinges required is listed in column I of the 'Item Info' sheet.

Release no. 2 has an order for (40) pieces of 1299B10015, (75) pieces of 1299B10018, (40) pcs of 1299B10021, etc. If you do the math manually, the total number of hinge pairs required to complete the orders in Release No. 2 is 495. if you now do the math for all of the Release No's entered into the 'Summary Sheet' form (2, 8, 18, 8, 18), the total number of hinge pairs required will be 1135 pcs. I've entered this number manually on the 'Summary' sheet in bold (in the 'Material's' section, next to the 'Hinges' label). I need to come up with a formula that will match the Release Number, Part Number, Part Quantity & Required Hinges per Part in order to get to this number.

This is a problem that is quite a bit more complex than I've ever encountered in Excel. Any help would be appreciated.

Thank you,
James