Multiple criteria lookup functions

Hi,

I've attached a sample of the data I'm using.

I have two spreadsheets (the samples for which I have shown side by side in Sheet 1 of the attached file).

Spreadsheet 1 is about 30,000 rows and too large for me to change the formatting and structure.

Spreadsheet 2 is the output I need and the format is required by other stakeholders.


In spreadsheet 1 I want to sum quantity in stock for Type 1, Type 2 and Type 3 for each product and allocate it to spreadsheet 2 according to the month in which the product expires. For example, there will be a total of 92 units of product 413302 which will expire in Nov, 2014. Therefore I want 92 to be placed in cell N6 of Spreadsheet 2.


Unfortunately the product number is not unique - there are multiple sub products in spreadsheet 1 but they all have the same quantities of stock. The sub products are referenced in other parts of the report so I can't consolidate by Product Number. This also prevents me from using the SUMIFS function as it will duplicate the number found in the sub products.

What I need, perhaps in a combination of functions, is to find the first instance of product 413302 in Spreadsheet 1 that is expiring in Nov 14, sum the product types and give the result in cell N6 of Spreadsheet 2.

Let me know if there is something I haven't explained properly. The answer is probably simple and I just can't see it.