Add date criteria matching order date and sales date

I have a workbook with 2 sheets, 1 for inventory and other for orders.

Because my buying prices are unstable i need to add some date criteria to it.

Let's imagine the follow example:


I order item No. A11, 10 pieces at total price 10$. 01/01/2014

Then i order again item No. A11, 10 pieces at total price 20$. 01/02/2014 (day/month/Year)


On my inventory workbook i have the unit price cell working as reference to all men monthly sales workbook to calculate my profit.

(unit price cell) =SUMIF(Orders!C:C;A5;Orders!F:F)/SUMIF(Orders!C:C;A5;Orders!D:D)

Problem is:

if i sell 10 items between 01/01/2014 and 01/02/2014 for 2€ each, my profit cell will show 1€ profit per item.
When i update my order sheet with the new order, adding 10 more pieces, ordered at 0,50$ piece, all my profit cells between 01/01/2014 and 01/02/2014 will now be 0,50$.
Also in this exemple, after 01/02/2014 my new sales should have a 0$ profit and not 0,50$.

Hi.

I use excel 2011 for mac.

I'll post the 2 workbooks.

templateInventory 2014.xlsxTemplateSales2014.xlsx