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?

 

[kweaver]

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.?

 

[Zot]

I guess it should be 100g, right?

It was a bit unclear. You want to set the Production Ledger (Qty) and find out the amount of ingredient or set the amount of ingredient and find qty of each product qty (which could be many possibility)?

 

[kweaver]

How about using XL2BB with a few examples? I thought my formula (filled down) did the product and sum you showed.

 

[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's 40g, sorry didn't proof read.

 

[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.?

What I'm looking to do is be able to calculate the total expected consumption as just 1 cell and 1 formula. So if my opening stock of cocoa was 200g, I'd like a formula that can just look at the production ledger and recipe quantities table to tell me what the expected closing stock should be. The way it is now, I'm using the very long version of the formula.

 

[Python49]

I guess it should be 100g, right?

It was a bit unclear. You want to set the Production Ledger (Qty) and find out the amount of ingredient or set the amount of ingredient and find qty of each product qty (which could be many possibility)?

To be more clear, I have an opening stock of Cocoa and then want a formula that can tell me the closing stock of cocoa according to every item that we produced for that day, how many of them, and how much cocoa it used. So the amount of cocoa each product uses is listed on the Recipe Quantities table and what we produced for the day is on the Production Ledger. Using these two tables of info, I was trying to get a formula to calculate the expected closing stock.

 

[kweaver]

Where is the opening stock?

 

[Python49]

Varies, but for this example we can say 200g of cocoa. Then according to what was entered on the production letter and then the recipe quantities I'm hoping to get an expected closing stock value.

 

[kweaver]

Well, the 200g has to be somewhere on a sheet in order to reference it and deduct according to what you're using.
What remains is this value (wherever it is) minus the used values I calculated.