Shopping list from meal plan: Summing multiple values

PhilCox12

New Member
Joined
Oct 10, 2017
Messages
6
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...?


MealIngredientMeasurement
Spaghetti BologneseSpaghetti500
Spaghetti BologneseMince meat250
Spaghetti BologneseOnion2
Spaghetti BolognesePepper2
 

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
Joined
Oct 24, 2012
Messages
4,677
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
4,013
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

New Member
Joined
Oct 10, 2017
Messages
6

ADVERTISEMENT

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:
MondayTuesdayWednesdayThursdayFridaySaturdaySunday
BreakfastDrop down list of breakfast recipesDrop down list of breakfast recipesDrop down list of breakfast recipes
Snack AMDrop down list of snack AM recipes
Lunc
Dinner
 

PhilCox12

New Member
Joined
Oct 10, 2017
Messages
6
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,677
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

i answered on the other forum
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,677
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Oct 24, 2012
Messages
4,677
Office Version
  1. 365
Platform
  1. MacOS
i assume you have a solution now , no reply for 24hrs
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top