Shortening repeating INDEX formulas with a consistent reference pattern. Possible?

[Monimonika]

I am making a spreadsheet that keeps track of how many rolls of each film item I have in inventory everyday. Some film items can be used in the production of multiple products.

While I do have the formulas working correctly, I wonder if there is a way to shorten a certain formula that calculates how much of each film item is used on a day (excluding negative production values).

I have attached a Sample sheet. The part highlighted in yellow on Sheet!2 contains the formulas I want to simplify/shorten.

Basically, on Sheet!2 I have for example FilmItem123

K4:O4 = Name of products that use FilmItem123 (some are deliberately blank)
K5:O5 = Row of each product in Sheet!1
K6:O6 = Pieces of film used in each product

In Sheet!2 row 2, there are also Column#s where each date is located in Sheet!1.
In Sheet!1 I have SampleProduction named range giving how much of each product was made each day (product items are not sorted).

My formula breakdown is as follows:

INDEX using Row (product) and Column (date) to get production value for a product for that day.
Production value above is multiplied by that product's pieces quantity to get pieces of product made.
MAX used to replace any negative values with zero.
Each of the products' pieces values are summed together and then divided by pcs/roll for film item to get how many rolls of film were used for that day.

Please Login or Register  to view this content.

My question is, is it possible to shorten the above formula? Some film items can have up to 10 or more products associated with them, and I have this feeling that the repeated pattern of the formula can be simplified even further.

I want it so that I can drag-copy the formula horizontally, as well as copy-paste into subsequent rows for different film items. Also, I would like to avoid using VBA or INDIRECT for something like this.

Thank you.

Sample.xlsx

[kvsrinivasamurthy]

ARRAY formula In E5, copied to other cells.

Please Login or Register  to view this content.

[samba_ravi]

oR
E5=SUMPRODUCT((Sheet1!$H$3:$H$9)*INDEX(Sheet1!$B$3:$F$9,,MATCH(E$1,Sheet1!$B$2:$F$2,0))*(INDEX(Sheet1!$B$3:$F$9,,MATCH(E$1,Sheet1!$B$2:$F$2,0))>0)*(Sheet1!$A$3:$A$9=Sheet2!$K4:$O4))/$B4

Please Login or Register  to view this content.

Try this and copy across

[Monimonika]

Awesomeness! Thank you to both kvsrinivasamurthy and nflsales for the two formulas. One used the references I had given and the other made it so CSE was not needed. I will be using either one or the other or a combination of both in my worksheet.

Again, thank you both soooo much!

- Monimonika