Get your orders into a shape you can add up
The trouble starts with a form where customers type their order: "2 chicken, 1 salmon". No formula can add that up. Change it to one number question per meal. A Google Form then gives you one row per order and one column per meal, and a SUM at the bottom of each column gives you your count.
That works until you add choices, such as portion sizes. Then keep a second tab with one row for each meal on each order, and four columns: customer, meal, option and quantity. To count it in Google Sheets, choose Insert, then Pivot table. Put meal and option under Rows, and the sum of quantity under Values. In Excel, it is PivotTable on the Insert tab.
Sample data.
Count what the kitchen makes
Your customers order meals, but your kitchen cooks chicken. So add a second table that turns meals into food. Make a small table with the meal, its count and the ounces of chicken in one. In this sample, 37 meals × 6 oz plus 11 meals × 8 oz is 310 oz of cooked chicken, or about 19.4 pounds.
You don't have to retype those counts. If your order rows are on a tab called Orders, with meal in column B, option in C and quantity in D, this counts the 6 oz chicken bowls in Sheets or Excel: =SUMIFS(Orders!D:D, Orders!B:B, "Chicken bowl", Orders!C:C, "6 oz").
Do this for anything that goes into more than one meal, such as rice and sauces. The cook needs one number for rice, not three numbers on three lines.
Then turn the pounds into pans, because that is how a cook thinks. If one pan holds 5 pounds, 19.4 pounds is 4 pans.
Freeze the numbers at cutoff
A list that keeps changing is how a kitchen ends up 4 meals short. At your order cutoff, copy the totals to a new tab with the date on it, and paste them as values only (Paste special in both Sheets and Excel), so the numbers stop changing. Anything that comes in later goes on a written list that one person owns.
Check it before you cook
- Add it up two ways. The total on the prep sheet should match the total quantity on the order rows. If they differ, a row was missed or a name was typed two ways.
- Watch for spelling. "Chicken bowl" and "chicken bowl " with a space are two meals to a pivot table. A dropdown on the form or the sheet stops this.
- Pull out allergies and special notes first. A note in a comment cell does not show up in a total. Give them their own list, and put it where the cook can see it.
Keep the sheet working as you grow
- Start each week from a clean copy. Keep one master tab with your formulas and no orders. Copy it for the new week, so last week's mistake doesn't come along.
- Lock your formulas. One formula typed over can stay wrong for weeks. In Sheets, use Data, then Protect sheets and ranges. In Excel, unlock the cells you type in first (Format Cells, then the Protection tab), then use Protect Sheet on the Review tab. Keep your pivot table on a tab you don't protect, and choose Refresh All on the Data tab when the orders change, because Excel won't update it by itself.
- Add a column for the delivery day. With a second delivery day, put that column in your pivot table too. The cook can then see each day's count on its own.
- Add choices one at a time. Each new choice adds rows and formulas. Add one, then check your totals the two ways above before you add the next.
If you are thinking about a general online store, know what you would get. Shopify and Square both report how many of each product sold in a date range, and you can export that. We found nothing in either one that adds up your portions or ingredients, so you would still build the second table above.
The Eat Fresh Tech way
Here's how we turn your orders into a prep list.
You pick a menu and open the Totals Report. There is no sheet to clean up first, because customers choose from your menu and the site records each choice the same way every time.
- The count. The Totals Report counts every meal, option and combination of options. With custom meals, it counts each component too, so you read "how many 6 oz chicken portions" straight off the page. You can filter by location and print it.
- More than one delivery day. The Daily Prep Report breaks the same counts down by day.
- A check. The Comparison Report compares the totals with the orders line by line.
The totals are built from the current orders when you open or refresh the report, with a line that says how current they are. Take what's already on your shelf off the count, and you're ready to cook.