shadowhound
New Member
 Joined
 Mar 8, 2021
 Messages
 4
 Office Version

 365
 2010
 Platform

 Windows
Hi,
I am building a cooking spreadsheet which calculates the nutritional content of various recipes. My spreadsheet has an ‘Ingredients’ tab and multiple tabs for each recipe. In each recipe tab, I have a list of the relevant ingredients, whose nutritional info I am importing from the ‘Ingredients’ tab using index match and multiplying by the number of servings (Intermediate Step). I then sum up the nutritional data across the ingredients using a sumif. (Final Step)
I am looking for a formula which would combine the intermediate step and the final step. The formula would import the nutritional data for the ingredients, multiply the nutritional data by the number of servings, and then sum up the data, separately for each nutrient.
As an example, I would input the ingredients, the number of servings for each ingredient, and the formula would output the total number of Calories in the recipe. I would then repeat this formula for every nutrient.
Is this possible? I attached a simplified version of the spreadsheet.
Thank you in advance.
I am building a cooking spreadsheet which calculates the nutritional content of various recipes. My spreadsheet has an ‘Ingredients’ tab and multiple tabs for each recipe. In each recipe tab, I have a list of the relevant ingredients, whose nutritional info I am importing from the ‘Ingredients’ tab using index match and multiplying by the number of servings (Intermediate Step). I then sum up the nutritional data across the ingredients using a sumif. (Final Step)
I am looking for a formula which would combine the intermediate step and the final step. The formula would import the nutritional data for the ingredients, multiply the nutritional data by the number of servings, and then sum up the data, separately for each nutrient.
As an example, I would input the ingredients, the number of servings for each ingredient, and the formula would output the total number of Calories in the recipe. I would then repeat this formula for every nutrient.
Is this possible? I attached a simplified version of the spreadsheet.
Thank you in advance.
Cooking Spreadsheet.xlsx  

B  C  D  E  F  G  H  I  
4  Number of Meals  3  
5  
6  Step 1: Ingredient Entry  
7  Ingredients:  Black Beans  Olive Oil  Onion  White Mushroom  Quinoa  Chicken Broth  Chicken Breast  
8  Unit (g)  100  100  100  100  100  240  100  
9  Servings  4.2  0.05  4  3.5  1.35  1.5  6.8  
10  Total (g)  420  5  400  350  135  360  680  
11  
12  Step 2: Importing Nutritional Info from Ingredients Tab  
13  Calories  382.2  44.2  160.0  77.0  496.8  7.5  680.0  
14  Fat (g)  1.3  5.0  0.4  1.1  8.2  0.0  17.7  
15  Saturated Fat (g)  0.4  0.7  0.2  0.4  0.9  0.0  4.1  
16  Monounsatured Fat (g)  0.0  3.7  0.1  0.0  0.0  0.0  4.7  
17  Polyunsaturated Fat (g)  0.0  0.5  0.1  0.0  0.0  0.0  2.9  
18  Cholesterol (mg)  0.0  0.0  0.0  0.0  0.0  0.0  496.4  
19  Total Carbohydrate (g)  71.4  0.0  37.2  11.6  86.4  0.0  0.0  
20  
21  Step 3: Sum of the Ingredients  
22  Unit  
23  Calories  615.9  
24  Fat (g)  g  11.2  
25  Saturated Fat (g)  g  2.2  
26  Monounsatured Fat (g)  g  2.8  
27  Polyunsaturated Fat (g)  g  1.2  
28  Cholesterol (mg)  mg  165.5  
29  Total Carbohydrate (g)  g  68.9  
QuinoaBlackBeans 
Cell Formulas  

Range  Formula  
C8:I8  C8  =INDEX(Ingredients!$B$4:$ZW$12,MATCH('QuinoaBlackBeans'!$B8,Ingredients!$B$4:$B$12,0),MATCH('QuinoaBlackBeans'!C$7,Ingredients!$B$4:$ZX$4,0)) 
C10:I10  C10  =+C8*C9 
C13:H19  C13  =INDEX(Ingredients!$B$6:$H$12,MATCH('QuinoaBlackBeans'!$B13,Ingredients!$B$6:$B$12,0),MATCH('QuinoaBlackBeans'!C$7,Ingredients!$B$4:$H$4,0))*C$9 
I13:I19  I13  =INDEX(Ingredients!$B$6:$W$12,MATCH('QuinoaBlackBeans'!$B13,Ingredients!$B$6:$B$12,0),MATCH('QuinoaBlackBeans'!I$7,Ingredients!$B$4:$X$4,0))*I$9 
D23:D29  D23  =SUM(OFFSET($C$12,MATCH($B23,$B$13:$B$19,0),0):OFFSET($Z$12,MATCH($B23,$B$13:$B$19,0),0))/$C$4 
Cooking Spreadsheet.xlsx  

B  C  D  E  F  G  H  I  
4  Black Beans  Quinoa  White Mushroom  Olive Oil  Onion  Chicken Broth  Chicken Breast  
5  Unit (g)  100  100  100  100  100  240  100  
6  Calories  91.0  368.0  22  884  40  5  100  
7  Fat (g)  0.3  6.1  0.3  100  0.1  0  2.6  
8  Saturated Fat (g)  0.1  0.7  0.1  14  0.0  0.0  0.6  
9  Monounsatured Fat (g)  0  0  0  73  0.0  0.0  0.7  
10  Polyunsaturated Fat (g)  0  0  0  10.5  0.0  0.0  0.4  
11  Cholesterol (mg)  0  0  0  0  0  0  73  
12  Total Carbohydrate (g)  17.0  64  3.3  0  9.3  0  0  
Ingredients 