VBA to sumproduct & loop...I think

bademployee

Board Regular
Joined
Aug 19, 2010
Messages
184
Hi all,

Sheet1 has product Bill of Materials:


Sheet2 is for user to input Product & Tonnes in the INPUT section:


I'm looking for VBA to calculate & return Ingredients & total Tonnes under the RESULT section.

Thanks for any help in advance.

Mark
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
This?

Book1
ABCDE
1
2Ingredient AIngredient BIngredient C
3Product 10.30.30.4
4Product 20.10.50.4
5Product 30.650.250.1
6
7
8ProductTonnesIngredientTonnes
9Product 2265Ingredient A43.9
10Product 158Ingredient B149.9
11Ingredient C129.2
Sheet10
Cell Formulas
RangeFormula
E9:E11E9=SUMPRODUCT(COUNTIF($A$9:$A$10,$A$3:$A$5)*SUMIF($A$9:$A$10,$A$3:$A$5,$B$9:$B$10)*OFFSET($B$3:$B$5,,ROWS(A$1:A1)-1))
 
Last edited by a moderator:
Upvote 0
Awesome, returns the OUTPUT tonnes spot on - thanks!

If I wanted to go one step further so that D9:D11 shows used Ingredients only, can this be done via formula? Above isn't a good example as all ingredients were used, in reality I'd apply this to hundreds of products with 50 or so ingredients.

Many thanks
 
Upvote 0
It's easier if your bill of materials is in single column format rather than a matrix. With Ingredient B as zero for products 1 and 2:

Book1
ABCDEFG
1RowColumnPercentTonnesProductTonnes
2Product 1Ingredient A0.634.8Product 2265
3Product 1Ingredient B00Product 158
4Product 1Ingredient C0.423.2
5Product 2Ingredient A0.126.5
6Product 2Ingredient B00
7Product 2Ingredient C0.9238.5
8Product 3Ingredient A0.650Row LabelsSum of Tonnes
9Product 3Ingredient B0.250Ingredient A61.3
10Product 3Ingredient C0.10Ingredient C261.7
Sheet13
Cell Formulas
RangeFormula
D2:D10D2=IFERROR(VLOOKUP(A2,$F$2:$G$3,2,0),0)*C2


fxdDQJU.png



Here's one way to convert the matrix:

 
Last edited by a moderator:
Upvote 0
Better formula:
Book1
ABCDE
1
2Ingredient AIngredient BIngredient C
3Product 10.30.30.4
4Product 20.10.50.4
5Product 30.650.250.1
6
7
8ProductTonnesIngredientTonnes
9Product 2265Ingredient A43.9
10Product 158Ingredient B149.9
11Ingredient C129.2
Sheet10
Cell Formulas
RangeFormula
E9:E11E9=SUMPRODUCT(SUMIF($A$9:$A$10,$A$3:$A$5,$B$9:$B$10)*($B$2:$D$2=D9)*$B$3:$D$5)
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top