VBA to sumproduct & loop...I think

[bademployee]

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

 

[sheetspread]

This?

Book1
	A	B	C	D	E
1
2		Ingredient A	Ingredient B	Ingredient C
3	Product 1	0.3	0.3	0.4
4	Product 2	0.1	0.5	0.4
5	Product 3	0.65	0.25	0.1
6
7
8	Product	Tonnes		Ingredient	Tonnes
9	Product 2	265		Ingredient A	43.9
10	Product 1	58		Ingredient B	149.9
11				Ingredient C	129.2
Sheet10

Cell Formulas
Range	Formula
E9:E11	E9	=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))

 

[bademployee]

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

 

[sheetspread]

I would just add a filter to hide the zeroes, a formula will be bulky and slow.

 

[bademployee]

Yeah fair point, thanks for your help!

 

[sheetspread]

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
	A	B	C	D	E	F	G
1	Row	Column	Percent	Tonnes		Product	Tonnes
2	Product 1	Ingredient A	0.6	34.8		Product 2	265
3	Product 1	Ingredient B	0	0		Product 1	58
4	Product 1	Ingredient C	0.4	23.2
5	Product 2	Ingredient A	0.1	26.5
6	Product 2	Ingredient B	0	0
7	Product 2	Ingredient C	0.9	238.5
8	Product 3	Ingredient A	0.65	0		Row Labels	Sum of Tonnes
9	Product 3	Ingredient B	0.25	0		Ingredient A	61.3
10	Product 3	Ingredient C	0.1	0		Ingredient C	261.7
Sheet13

Cell Formulas
Range	Formula
D2:D10	D2	=IFERROR(VLOOKUP(A2,$F$2:$G$3,2,0),0)*C2

Here's one way to convert the matrix:

 

[sheetspread]

Better formula:

Book1
	A	B	C	D	E
1
2		Ingredient A	Ingredient B	Ingredient C
3	Product 1	0.3	0.3	0.4
4	Product 2	0.1	0.5	0.4
5	Product 3	0.65	0.25	0.1
6
7
8	Product	Tonnes		Ingredient	Tonnes
9	Product 2	265		Ingredient A	43.9
10	Product 1	58		Ingredient B	149.9
11				Ingredient C	129.2
Sheet10

Cell Formulas
Range	Formula
E9:E11	E9	=SUMPRODUCT(SUMIF($A$9:$A$10,$A$3:$A$5,$B$9:$B$10)*($B$2:$D$2=D9)*$B$3:$D$5)