How would I go about adding up all the raw ingredients sold per items with different proportions?
I haven't used Excel/Sheets for a while and I'm pretty rusty at the moment, even though I think there's a simply solution to this.
I have a table with a column that has every item on the menu and rows next to it indicating the portion of each raw ingredient that item takes to make.
At the end of the day I want to enter in the Amount Sold cell that day's sales and have a separated row or list of the total of each raw ingredient portion sold
Example of how the table is formatted (the ingredients quantity shown are in grams):
| Item | Amount Sold | Salmon | Rice | Avocado | Crab | Fish |
|---|---|---|---|---|---|---|
| California Roll | 2 | 200 | 80 | 100 | ||
| Alaska Roll | 4 | 200 | 200 | 100 | ||
| Half Alaska Roll | 10 | 100 | 100 | 50 | ||
| Ceviche | 5 | 150 | 300 |
This is kind of what I want to show at the end of all the rows
| Sales | Items Sold | Salmon | Rice | Avocado | Crab | Fish |
|---|---|---|---|---|---|---|
| 21 | 1800 | 2200 | 1060 | 200 | 1500 |
I wish I could do a for loop and to go thru each row MULTIPLY the Amount Sold cell by each used ingredient and then add all the cells results.
Thanks!
Use SUMPRODUCT:
=SUMPRODUCT($B$2:$B$5,C2:C5)
If the order of products is not the same then we can add an INDEX/MATCH:
=SUMPRODUCT($B$2:$B$5,INDEX($C$2:$G$5,0,MATCH(B$8,$C$1:$G$1,0)))
The INDEX/MATCH returns the correct column to the SUMPRODUCT.
