Which function(s) can be used for this?

[Python49]

A bit stumped on this one, although I feel like it's probably just a basic combination of sumproduct, VLOOKUP, index or something else that likely can make this quite easy. Although I could be wrong. The situation is pretty straight forward:

I have a chart that lists out different flavors of cookies on column A and then ingredients listed across row 1. It's a table that just allows each product to list out the quantity used for each ingredient. So for instance, on the column A, there might be 3 different types of chocolate. 1) Chocolate 2) Double Chocolate 3) Triple Chocolate. The normal chocolate uses 10g cocoa, double 20g, triple 30g. I have a table like this.

A- (Recipe Quantity)	B	C	D
	Cocoa	Vanilla	Strawberry
Chocolate	10	-	-
Double Chocolate	20	-	-
Triple Chocolate	30	-	-

Then for the production ledger for the day, I want to be able to list out the different quantities of cookies we made and let the formula just total the total cocoa used. So if we made 3 normal chocolate (30g), 2 double (60g), and 1 triple (30g) that's a total of 120g consumed.

Production Ledger	Quantity
Chocolate	3
Double Chocolate	2
Triple Chocolate	1

I was doing this manually previously for a while by just adding a new ingredient and then manually doing a formula such as:

=SUMIF ( Production Ledger'!A:A , Production Ledger'!A3 (cookie being made), Production Ledger'!B:B) <--- gives cookie type quantity * SUMIF( 'Recipe Quantity'!A:A, 'Production Ledger'!A3, 'Recipe Quantity'!B:B)<-- cocoa per cookie
SUMIF ( Production Ledger'!A:A , Production Ledger'!A4, Production Ledger'!B:B) * SUMIF( 'Recipe Quantity'!A:A, 'Production Ledger'!A4, 'Recipe Quantity'!B:B)
SUMIF ( Production Ledger'!A:A , Production Ledger'!A5, Production Ledger'!B:B) * SUMIF( 'Recipe Quantity'!A:A, 'Production Ledger'!A4, 'Recipe Quantity'!B:B)

So then I just end up with an incredibly long formula but it gets the job done. But it's become unsustainable. Any help?

 

[Zot]

Something like this?

Book1
	A	B	C	D
2	Material	Cocoa	Vanilla	Strawberry
3	Quantity	200
4	Usage	120
5	Balance	80
6
7
8	A- (Recipe Quantity)	B	C	D
9		Cocoa	Vanilla	Strawberry
10	Chocolate	10	-	-
11	Double Chocolate	20	-	-
12	Triple Chocolate	30	-	-
13
14
15
16	Production Ledger	Quantity
17	Chocolate	3
18	Double Chocolate	3
19	Triple Chocolate	1
Sheet1

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT(B10:B12,B17:B19)
B5	B5	=B3-B4

 

[Python49]

Isn't it 3 normal chocolate (30g), 2 double (40g), and 1 triple (30g) that's a total of 100g consumed.

Does this work?

Book18
	A	B	C	D
1	A- (Recipe Quantity)	B	C	D
2		Cocoa	Vanilla	Strawberry
3	Chocolate	10	2	0
4	Double Chocolate	20	0	0
5	Triple Chocolate	30	0	0
Recipe

Book18
	A	B	C	D
1	Production Ledger	Quantity
2	Chocolate	3		36
3	Double Chocolate	2		40
4	Triple Chocolate	1		30
Production

Cell Formulas
Range	Formula
D2:D4	D2	=SUMPRODUCT(B2*Recipe!B3:D3)

or is the product more straight-forward than this? Just 3 times the cocoa, etc.?

Yes it seems that you added additional data to another column. I was hoping to just have everything in one cell since the number of different ingredients will be a lot, so having an additional column for each ingredient would become a lot. There will be like 200 ingredients listed that I'm looking to get the opening and closing stock for.

 

[Zot]

Yes it seems that you added additional data to another column. I was hoping to just have everything in one cell since the number of different ingredients will be a lot, so having an additional column for each ingredient would become a lot. There will be like 200 ingredients listed that I'm looking to get the opening and closing stock for.

Just want to show the formula. It would help if you show how your worksheet will looks like, just like kweaver asked.

I just created another table to illustrate the calculation

 

[Python49]

Something like this?

Book1
	A	B	C	D
2	Material	Cocoa	Vanilla	Strawberry
3	Quantity	200
4	Usage	120
5	Balance	80
6
7
8	A- (Recipe Quantity)	B	C	D
9		Cocoa	Vanilla	Strawberry
10	Chocolate	10	-	-
11	Double Chocolate	20	-	-
12	Triple Chocolate	30	-	-
13
14
15
16	Production Ledger	Quantity
17	Chocolate	3
18	Double Chocolate	3
19	Triple Chocolate	1
Sheet1

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT(B10:B12,B17:B19)
B5	B5	=B3-B4

Yes, but the recipe quantities and production ledger are on different sheets and of different range sizes. Also would need the formula to match on it's own which values to sumproduct.

 

[Python49]

Just want to show the formula. It would help if you show how your worksheet will looks like, just like kweaver asked.

I just created another table to illustrate the calculation

Yes, will try to just send this. Didn't have the plugin setup yet and wasn't home yet. Will try to just upload this when I get back.