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
Hamburger
Double Hamburger
Cheeseburger
Double cheeseburger
Start
Bun
Patty
Cheese
Ketchup
Mustard
Pickle
Onions
Bun	Patty	Cheese	Ketchup	Mustard	Pickle	Onions
Hamburger
Double Hamburger
Cheeseburger
Double cheeseburger

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Weeks[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Sales[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Weeks[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Stock[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]52[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]51[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"]-90[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]Burger ingredients Matrix[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D10[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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