Shopping list from meal plan: Summing multiple values

PhilCox12

New Member
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

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

etaf

Well-known Member
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

MrExcel MVP, Moderator
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.

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

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

PhilCox12

New Member

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

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

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

Well-known Member

i answered on the other forum

etaf

Well-known Member
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

Cell Formulas
RangeFormula
A2:A20A2=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:B20B2=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:A36A21=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:B36B21=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:H21H2=UNIQUE(FILTER(\$A\$2:\$A\$1112,\$A\$2:\$A\$1112<>""))
I2:I30I2=SUMIF(A:A,H2,B:B)
Dynamic array formulas.

etaf

Well-known Member
i assume you have a solution now , no reply for 24hrs

Replies
4
Views
126
Replies
0
Views
237
Replies
0
Views
229
Replies
2
Views
250
Replies
6
Views
700

1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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.

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

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