The "Burger" question

[szita2000]

Hi All.

It is the end of Friday and my Brain just mushy.
Here is what I am trying to figure out.
Background:
At work we have machines that are using raw materials.
Some of them only uses 1, others 2,3, 4 blend of raw materials.
Each machine uses different amount from each material for a day's worth of production.
I started to build a Material tracker that pulls together the usage on each material by all the machines that was running daily and takes the amount off from the material sheet.
- So I can see when I need to order materials.

I got entangled in it.
So I was thinking to make my life easier I made a simplified version of my problem, in terms of Hamburgers.


Hamburgers have different ingredients also different burgers require different ingredients. Right?

What I managed to figure out is that I will need:

• A Table that tracks the sales of each Hamburger (Representing each machine in real life) - Below this is table "C2:L6"
  
• Another table that tracks the stock (Representing my raw materials in real life) - Below this is table "B9:L16"
  
• And a Table that describes each Burger's recipe (Representing the raw materials go in to each machine daily in real life) - Below this is table "B18:I22"
  

My question is.
Is there any better way to write my formula in D10 with the individual SUMIF?
As in real life I have about 25 ingredients (Raw Materials) and about 40 type of burgers (machines). That would be a lot of individual SUMIF.

Thanks.

	A	B	C	D	E	F	G	H	I	J	K	L
1				Weeks
2				1	2	3	4	5	6	7	8	9
3		Sales	Hamburger	10	5
4			Double Hamburger		3
5			Cheeseburger	2	12
6			Double cheeseburger	5	12
7
8				Weeks
9			Start	1	2	3	4	5	6	7	8	9
10	Stock	Bun	100	83	51	51	51	51	51	51	51	51
11		Patty	100	78	31	31	31	31	31	31	31	31
12		Cheese	100	88	52	52	52	52	52	52	52	52
13		Ketchup	100	83	51	51	51	51	51	51	51	51
14		Mustard	100	83	51	51	51	51	51	51	51	51
15		Pickle	100	61	-21	-21	-21	-21	-21	-21	-21	-21
16		Onions	100	39	-90	-90	-90	-90	-90	-90	-90	-90
17
18	Burger ingredients Matrix		Bun	Patty	Cheese	Ketchup	Mustard	Pickle	Onions
19		Hamburger	1	1		1	1	2	3
20		Double Hamburger	1	2		1	1	4	6
21		Cheeseburger	1	1	1	1	1	2	3
22		Double cheeseburger	1	2	2	1	1	3	5

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D10 =C10-((D$3*(SUMIF($C$18:$I$18,$B10,$C$19:$I$19)))+(D$4*(SUMIF($C$18:$I$18,$B10,$C$20:$I$20)))+(D$5*(SUMIF($C$18:$I$18,$B10,$C$21:$I$21)))+(D$6*(SUMIF($C$18:$I$18,$B10,$C$22:$I$22)))) <tbody> </tbody>

<tbody>
</tbody>

 

[XOR LX]

Hi,

Perhaps you've made the mistake of oversimplifying your example for the sake of this forum, since the following would seem to give the same results as you have:

=C10-SUMPRODUCT(D$3:D$6,INDEX($C$19:$I$22,,MATCH($B10,$C$18:$I$18,0)))

Regards

 

[Logit]

.
I started to answer your question ... but ate it instead.

Thank you !

 

[szita2000]

Thanks Xor Lx!
****. I need to study up on sumproduct.

 

[XOR LX]

You're welcome!

Cheers