Vlookup + Sumifs based on date range

Hello Everyone,

Appreciate if anyone could help me with the following attachment.

Am currently working on an excel document that has been created to capture budget requirements; the workbook has 4 worksheets.

The first worksheet captures the cost of batches produced under each product, per month.
The second worksheet captures the sum of all the batches that fall under each product per month.
The third worksheet captures the quarterly production cost of each product.
The fourth worksheet captures the annual production cost of each product.

Overall aim: Improve the functionality of the excel document.

First Issue
The second worksheet (monthly) is based on the HLOOKUP function. A limitation to this workbook is that it should be flexible enough to have batches added/removed as required. Hence, HLOOKUP was used, however, every time a batch is added or removed, the number of rows in the far right have to be adjusted. Therefore, a formula other than hlookup should be applied.

Second Issue
The third worksheet (quarterly) is based on row numbers. If a product is added, the user will have to insert a new row in the batches worksheet, the monthly worksheet and subsequently, in this quarterly sheet. This will shift all rows downwards and will render all figures as inaccurate.

I am looking for a formula that is based on the product name. The formula will look up the product name in the monthly sheet, go to the row with the monthly costs and sum of the costs that fall within dates.

For example, for Q1 2015 for product A, the formula should produce the costs that are for product A between 1 Jan to 30 June in the cell.

Third Issue
The third worksheet (annual) is based on the row number as well, hence, poses the same tendency towards producing errors in the instance a product is added. A formula that is similar to the one that is applied in the quarterly sheet should be applied here.


I sense that the formula that I am looking for is a combination of vlookup, sumif and most likely an array, however, I am not able to put my finger on it.


Appreciate any help.

Feel free to provide suggestions/comments on areas in which this document can be improved.

see the attached file

I advice you to re-structure the data in a verticaly way.

After that it is very easy to analyze the data (e.g. with an pivot table).

See the attached file.

You are welcome, thanks for your feedback and adding reputation