szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Hi All.

It is the end of Friday and my Brain just mushy. :oops:
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.



ABCDEFGHIJKL
1Weeks
2123456789
3SalesHamburger105
4Double Hamburger3
5Cheeseburger212
6Double cheeseburger512
7
8Weeks
9Start123456789
10StockBun100835151515151515151
11Patty100783131313131313131
12Cheese100885252525252525252
13Ketchup100835151515151515151
14Mustard100835151515151515151
15Pickle10061-21-21-21-21-21-21-21-21
16Onions10039-90-90-90-90-90-90-90-90
17
18Burger ingredients MatrixBunPattyCheeseKetchupMustardPickleOnions
19Hamburger111123
20Double Hamburger121146
21Cheeseburger1111123
22Double cheeseburger1221135

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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>
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,856
.
I started to answer your question ... but ate it instead.

Thank you !

:rolleyes:
 

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Thanks Xor Lx!
****. I need to study up on sumproduct.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,889
Messages
5,598,672
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top