Hi Everyone,
I'm looking for some help to simplify the following:
1) I have 1 sheet with a large table A1:D4 showing sales volume (Mt) per productcode (column A) and per months of June, July and August (row 1)
<tbody>
</tbody>
2) I have a 2nd sheet with a large table A1:E4 showing the % of each ingredients (row 1) that the productcodes (column A) are composed.
<tbody>
</tbody>
Goal: I'm looking to summarize in 1 (total) cell for each month, the volume (Mt) of the ingredients needed.
So far I managed only to work out ingredient per ingredient, month per month by pulling down -and right- something like the following formula.
Unfortunately this takes up more space than the original tables themselves.
INDEX(Sheet2!B2:E4;MATCH("ProductCode1";Sheet2!A2:A4;0);MATCH("Ingredient1";Sheet2!B1:E1;0))*INDEX(Sheet1!B2:D4;MATCH("June";Sheet1!B1:D1;0))
Would anyone have a better solution, that would allow me to summarize the ingredient volume (Mt) in 1 cell per month?
Thanks in advance for anyone willing to help out!
I'm looking for some help to simplify the following:
1) I have 1 sheet with a large table A1:D4 showing sales volume (Mt) per productcode (column A) and per months of June, July and August (row 1)
June | July | August | |
ProductCode1 | 100 | 50 | 200 |
ProductCode2 | 150 | 80 | 120 |
ProductCode3 | 200 | 90 | 350 |
<tbody>
</tbody>
2) I have a 2nd sheet with a large table A1:E4 showing the % of each ingredients (row 1) that the productcodes (column A) are composed.
Ingredient1 | Ingredient2 | Ingredient3 | Ingredient4 | |
ProductCode1 | 20% | 5% | 0% | 75% |
ProductCode2 | 80% | 20% | 0% | 0% |
ProductCode3 | 10% | 20% | 30% | 40% |
<tbody>
</tbody>
Goal: I'm looking to summarize in 1 (total) cell for each month, the volume (Mt) of the ingredients needed.
So far I managed only to work out ingredient per ingredient, month per month by pulling down -and right- something like the following formula.
Unfortunately this takes up more space than the original tables themselves.
INDEX(Sheet2!B2:E4;MATCH("ProductCode1";Sheet2!A2:A4;0);MATCH("Ingredient1";Sheet2!B1:E1;0))*INDEX(Sheet1!B2:D4;MATCH("June";Sheet1!B1:D1;0))
Would anyone have a better solution, that would allow me to summarize the ingredient volume (Mt) in 1 cell per month?
Thanks in advance for anyone willing to help out!