I just finished your Numbers course and it was great! Unfortunately, I don’t think what I’m looking for was included(probably wrong on that one). Also, i am still trying to understand the language and logic of the formulas, so that’s why I’m asking for a bit of assistance.
I hope its not too convoluted!
I am trying to make a monthly inventory spreadsheet for my brewery. The front facing table (orders) will have separate pull down columns for client, beer and package (1/2 keg, 1/6 keg, case and 4 pack), with a column at the end for actual amount ordered of that particular beer and package. So an example would be:
Client Beer Package Amount
Brett Pilsner 1/2 keg 2
I have another table with the actual inventory. Our Beers in the header column and 3 columns for for each package (i.e. 1/2 keg Begin (for begin of month), 1/2 Keg Add (for any beer made that month) and 1/2 keg current (which would be the subtracted total of all orders pertaining to that beer and package size):
BEER 1/2 Keg Begin 1/2 Keg Add 1/2 Keg Current
Pilsner 10 10 ?
That’s where my problem starts. From the Orders table, I would like the add all the order amounts of a particular beer and package size, to be added together then subtracted from the sum of the 1/2 keg begin and 1/2 keg Add.
I realize this is a bit insane and understand if you cant make heads or tails of it. I tried to include screen shots here, but no go.
—–
Brett Blau
So first, I would do the second table differently or you will run into trouble. I would have columns for: Beer, Package, Begin, Add, Orders, End. That way each row will represent a beer and package size. So the first row would be Pilsner and 1/2 Keg, for instance.
The columns Begin and Add are filled in by you, I assume. So that leaves Order Amounts, which will be the sum of all of the orders that match that beer and package. You would use SUMIFS for that. Look up the SUMIFS function to see examples.
In this case, it would look like this (in cell E2):
SUMIFS(Amount,Orders::Beer,A2,Orders::Package,B2)
The first parameter is the Orders::Amount column. The next two are the first comparison. You are comparing Orders::Beer with A2 (the beer cell for that row). Then the next pair are comparing Orders:Package with B2 (the package for that row). So you get the sum of amounts from the Orders table where the beer and package match.
Now that you have your Order Amounts, you can just have a simple formula in the End column to take Begin + Add - Order Amounts. If you really don't want to see the Order Amounts, you can always hide that column.
Holy Mackerel it worked! If you are ever in southern Long Island, please stop by the brewery for some inventoried beer, on me!
Thank you very much,
Brett