Shopping list from meal plan: Summing multiple values

[PhilCox12]

Hey everyone!

Basically as part of my weight loss journey I am trying to create a bit more of a savvy meal plan so I don't have to rely on the same few meals week-in week-out. I've done all the data for this but would like to link it into a weekly shopping list so I can order this easily without having to go through each recipe. Basically as of now I have two tables, one as a meal plan (Monday > Sunday; Breakfast, Snack, Lunch, Snack, Dinner). The second tab is the ingredients for each recipe, ordered as per the below (an example).

So using the below example as the template for how the recipes are structured, I want to auto calculate the shopping list of ingredients based on the recipes in the meal plan (of which the names will be perfectly aligned to the name in Column A (meal).

Any advice on how this can be done? My thoughts are to list all the ingredients in a third tab and then use some formula to look up the recipes in the meal plan and then sum up the ingredients but not sure how to create this as I don't think a vlookup / sumif will work...?

Meal	Ingredient	Measurement
Spaghetti Bolognese	Spaghetti	500
Spaghetti Bolognese	Mince meat	250
Spaghetti Bolognese	Onion	2
Spaghetti Bolognese	Pepper	2

 

[etaf]

My thoughts are to list all the ingredients in a third tab

then use a SUMIF() to total for that ingredient , providing the amount is in the same measurement ie Each, grams etc and you know what each ingrediant measure is

in ingredients/shopping tab
Column A2 has Onion
Then B2 would be SUMIF(
Assuming the ingredients / recipe is in Tab named Recipe
=SUMIF( recipe!B:B, A2, recipe!C:C)
where recipe tab has in A the Meal, B ingredient C measurement

You could add an indicator in the mealplan additional column as a add to shopping maybe an X
then use a SUMIFS() using the X as a criteria, so it will now only add up items with an X

=SUMIFS( recipe!C:C, recipe!B:B, A2, recipe!D:D, "X") Note the range to SUM is the start of the formula in this case not the end as in a sumif

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Shopping list from meal plan: Summing multiple values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Logit]

Rather than re-create the wheel ... have you considered using what has already been created ?

Google : menu shopping list

There are some excellent completed projects ready for your use.

 

[PhilCox12]

then use a SUMIF() to total for that ingredient , providing the amount is in the same measurement ie Each, grams etc and you know what each ingrediant measure is

in ingredients/shopping tab
Column A2 has Onion
Then B2 would be SUMIF(
Assuming the ingredients / recipe is in Tab named Recipe
=SUMIF( recipe!B:B, A2, recipe!C:C)
where recipe tab has in A the Meal, B ingredient C measurement

You could add an indicator in the mealplan additional column as a add to shopping maybe an X
then use a SUMIFS() using the X as a criteria, so it will now only add up items with an X

=SUMIFS( recipe!C:C, recipe!B:B, A2, recipe!D:D, "X") Note the range to SUM is the start of the formula in this case not the end as in a sumif

I get how this works to sum the totals of the recipes but it doesn't reference the meal in the meal plan? Right now I have drop down lists on a meal plan as per the below in the first sheet, with the ingredients on another sheet listed as per the above table. So based on what recipes are selected in the meal plan I want to sum-up the ingredients needed for the entire week.

Meal plan layout:

	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Breakfast	Drop down list of breakfast recipes	Drop down list of breakfast recipes	Drop down list of breakfast recipes
Snack AM	Drop down list of snack AM recipes
Lunc
Dinner

 

[PhilCox12]

Rather than re-create the wheel ... have you considered using what has already been created ?

Google : menu shopping list

There are some excellent completed projects ready for your use.

I did look on google but couldn't find one that both acted as a meal plan and automatically calculated the ingredients. There are plenty of meal plan templates but they all seemed to require a manual impact of copying pasting over the ingredients.

 

[etaf]

i answered on the other forum

 

[etaf]

I think my approach may not be very sustainable as things change
This is just an extract

At the moment on Shopping list, we need to find all the UNIQUE ingredients
So in A2 to A20 - I pull out the ingredients for the 1st meal ( I used 20 as thats cover the recipe with the most ingredients - BUT would need to relayout if a recipe in the future has more
Then in A21 to A40 - the 2nd meal and so
Now this is going to get huge
5 meals per day & 7 days = 5x7 = 35
Then pull out the quantity for each of those in B

Now we have a huge list which includes spaces
In H I have then Filtered and Unique - so we get a list of unique ingredients and no spaces, thats where I used the 365 UNIQUE / FILTER - otherwise it will need to be another index type formula

Then using SUMIF() to quantify the volume and effectively a Shopping list
BUT things like DASH/PINCH are not going to work

Also just for forum etiquette , I have updated here, But will stick to one forum in future I think otherwise again gets confusing
Plus i dont think this is going to work long term and not sure other than using a VBA solution how to progress , and i do not do VBA on forums

Meal Plan (ETAF).xlsx
	A	B	C	D	E	F	G	H	I
2	Strong White Flour (grams)	450						Strong White Flour (grams)	900
3	Yeast (grams)	14						Yeast (grams)	28
4	Caster Sugar (grams)	50						Caster Sugar (grams)	100
5	Warm Milk (ml)	150						Warm Milk (ml)	300
6	Egg (unit)	1						Egg (unit)	2
7	Unsalted Butter (grams)	50						Unsalted Butter (grams)	100
8	Oil (unit)	1						Oil (unit)	3
9	Cinnamon (unit)	1						Cinnamon (unit)	2
10	Ginger (unit)	1						Ginger (unit)	2
11	Dried Apricot (grams)	50						Dried Apricot (grams)	100
12	Dried Fig (grams)	50						Dried Fig (grams)	100
13	Pitted Dates (grams)	50						Pitted Dates (grams)	100
14	Sultana (grams)	50						Sultana (grams)	100
15	Glace Cherry (grams)	50						Glace Cherry (grams)	100
16	Orange (unit)	1						Orange (unit)	2
17								Banana (unit)	1
18								Eggs (unit)	2
19								Baking Powder (unit)	0
20								Vanilla Extract (unit)	0
21	Strong White Flour (grams)	450						Rasberry (grams)	125
22	Yeast (grams)	14							0
23	Caster Sugar (grams)	50							0
24	Warm Milk (ml)	150							0
25	Egg (unit)	1							0
26	Unsalted Butter (grams)	50							0
27	Oil (unit)	1							0
28	Cinnamon (unit)	1							0
29	Ginger (unit)	1							0
30	Dried Apricot (grams)	50							0
31	Dried Fig (grams)	50
32	Pitted Dates (grams)	50
33	Sultana (grams)	50
34	Glace Cherry (grams)	50
35	Orange (unit)	1
36
Shopping List

Cell Formulas
Range	Formula
A2:A20	A2	=IFERROR(INDEX('All Recipes'!$B$2:$B$200,SMALL(IF(('All Recipes'!$A$2:$A$200)='Meal Plan '!B$2,MATCH(ROW('All Recipes'!$A$2:$A$200),ROW('All Recipes'!$A$2:$A$200))),ROW(A1))),"")
B2:B20	B2	=IFERROR(INDEX('All Recipes'!$C$2:$C$200,SMALL(IF(('All Recipes'!$A$2:$A$200)='Meal Plan '!B$2,MATCH(ROW('All Recipes'!$A$2:$A$200),ROW('All Recipes'!$A$2:$A$200))),ROW(A1))),"")
A21:A36	A21	=IFERROR(INDEX('All Recipes'!$B$2:$B$200,SMALL(IF(('All Recipes'!$A$2:$A$200)='Meal Plan '!C$2,MATCH(ROW('All Recipes'!$A$2:$A$200),ROW('All Recipes'!$A$2:$A$200))),ROW(A1))),"")
B21:B36	B21	=IFERROR(INDEX('All Recipes'!$C$2:$C$200,SMALL(IF(('All Recipes'!$A$2:$A$200)='Meal Plan '!C$2,MATCH(ROW('All Recipes'!$A$2:$A$200),ROW('All Recipes'!$A$2:$A$200))),ROW(A1))),"")
H2:H21	H2	=UNIQUE(FILTER($A$2:$A$1112,$A$2:$A$1112<>""))
I2:I30	I2	=SUMIF(A:A,H2,B:B)
Dynamic array formulas.

 

[etaf]

i assume you have a solution now , no reply for 24hrs