Match criteria for calculating a summary of usage

[Anuru]

Dear experts,

Kindly look in to the attachment with this post. This is my planning module for the knitting section of my plant(sock manufacturing). Here, we have two types of machines 108 needle, and 144 needle. We need to plan both the types in a month and for orders. We have some 69 machines from 108 needle, and 51 machines from 144 needle.

I have a requirement to analyse and see the balance machines from each type(144 and/or 108) while we are planning the orders to avoid any inaccuracies that may occur due to "over planning" machines exceeding the available qty on a particular day/period.

For this purpose, I prepared an summary/ analysis below the work sheet. Here P33 to P63 and Q33 to Q63 shows the days of the month. For example, order 01 has been planned from Jan 1st to 15th Jan with 10 (144 needle) machines, and Order 02 has been planned from 2nd Jan to 25th Jan with 5(144 needle) machines. And also, order 03 has been planned 2nd jan to 28th Jan with 5 (108 needle machines). Accordingly, the table below shows the machine usage as per the machine type and time period.

I need your help to generate a formula that can be applied in "P" and "Q" columns that do this calculation.

Thanks in advance.

Anuruddha

[José Augusto]

Hi
try this in P33 and drag down and right

Please Login or Register  to view this content.

[Anuru]

Dear Jose,

Your solution is perfect. However, when I copy the same for the month of February, it repeats the same. Is there a way to create a link between the planning table and summary?

Regards
Anuruddha

[JohnTopley]

This a duplicate post (having replied to the one below)

http://www.excelforum.com/excel-form...her-cells.html

[sanram]

After copying cell P33 and cell Q33 to cell V33 and cell W33 change $O33 to $U33 in the formula of cell V33 and cell W33.

[Anuru]

Dear Sanram,

Thanks for your feedback..

Regards
Anuruddha

[sanram]

Dear Sanram,

Thanks for your feedback..

Regards
Anuruddha

You are most Welcomed