Looking to combine import using indexmatch with sumif

shadowhound

New Member
Joined
Mar 8, 2021
Messages
4
Office Version
  1. 365
  2. 2010
Platform
  1. 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.

Cooking Spreadsheet.xlsx
BCDEFGHI
4Number of Meals3
5
6Step 1: Ingredient Entry
7Ingredients:Black BeansOlive OilOnionWhite MushroomQuinoaChicken BrothChicken Breast
8Unit (g)100100100100100240100
9Servings4.20.0543.51.351.56.8
10Total (g)4205400350135360680
11
12Step 2: Importing Nutritional Info from Ingredients Tab
13Calories382.244.2160.077.0496.87.5680.0
14Fat (g)1.35.00.41.18.20.017.7
15Saturated Fat (g)0.40.70.20.40.90.04.1
16Monounsatured Fat (g)0.03.70.10.00.00.04.7
17Polyunsaturated Fat (g)0.00.50.10.00.00.02.9
18Cholesterol (mg)0.00.00.00.00.00.0496.4
19Total Carbohydrate (g)71.40.037.211.686.40.00.0
20
21Step 3: Sum of the Ingredients
22Unit
23Calories615.9
24Fat (g)g11.2
25Saturated Fat (g)g2.2
26Monounsatured Fat (g)g2.8
27Polyunsaturated Fat (g)g1.2
28Cholesterol (mg)mg165.5
29Total Carbohydrate (g)g68.9
Quinoa-BlackBeans
Cell Formulas
RangeFormula
C8:I8C8=INDEX(Ingredients!$B$4:$ZW$12,MATCH('Quinoa-BlackBeans'!$B8,Ingredients!$B$4:$B$12,0),MATCH('Quinoa-BlackBeans'!C$7,Ingredients!$B$4:$ZX$4,0))
C10:I10C10=+C8*C9
C13:H19C13=INDEX(Ingredients!$B$6:$H$12,MATCH('Quinoa-BlackBeans'!$B13,Ingredients!$B$6:$B$12,0),MATCH('Quinoa-BlackBeans'!C$7,Ingredients!$B$4:$H$4,0))*C$9
I13:I19I13=INDEX(Ingredients!$B$6:$W$12,MATCH('Quinoa-BlackBeans'!$B13,Ingredients!$B$6:$B$12,0),MATCH('Quinoa-BlackBeans'!I$7,Ingredients!$B$4:$X$4,0))*I$9
D23:D29D23=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
BCDEFGHI
4Black BeansQuinoaWhite MushroomOlive OilOnionChicken BrothChicken Breast
5Unit (g)100100100100100240100
6Calories91.0368.022884405100
7Fat (g)0.36.10.31000.102.6
8Saturated Fat (g)0.10.70.1140.00.00.6
9Monounsatured Fat (g)000730.00.00.7
10Polyunsaturated Fat (g)00010.50.00.00.4
11Cholesterol (mg)00000073
12Total Carbohydrate (g)17.0643.309.300
Ingredients
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
See if this works for you:
  1. Replace all sheet name references which refer to the current sheet
    Replace 'Quinoa-BlackBeans'! with nothing in replace box
    When you build a formula referring to other sheets using the mouse, is also puts the sheet name for the cell references in the current workbook.
    These clutter up the formula making it look more complex and cause issues when you want to replicate the sheet using copy paste.

  2. Made the Ingredients table into an Excel Table
    This required having a heading for the first column, I have used type
    (you can change that later but if you want to copy paste my formulas you need to do it after replicating everything.
    Convert the data to a table
    Give it the table name tbl_ingredients
    (again you can change this at the very end)


Ingredients Sheet with Heading for Column 1 and converted to a Table called tbl_Ingredients

20210309 Cooking Ingredients lis.xlsx
ABCDEFGHIJ
1
2
3
4TypeBlack BeansQuinoaWhite MushroomOlive OilOnionChicken BrothChicken Breast
5Unit (g)100100100100100240100
6Calories9136822884405100
7Fat (g)0.36.10.31000.102.6
8Saturated Fat (g)0.10.70.1140.04200.6
9Monounsatured Fat (g)000730.01300.689
10Polyunsaturated Fat (g)00010.50.01700.424
11Cholesterol (mg)00000073
12Total Carbohydrate (g)17643.309.300
13
Ingredients


Quinoa-BlackBeans after formula modifications and refers to Ingredients Table Name - So create Ingredients Table and Table name before copying

20210309 Cooking Ingredients lis.xlsx
ABCDEFGHIJ
1
2
3
4Number of Meals3
5
6Step 1: Ingredient Entry
7Ingredients:Black BeansOlive OilOnionWhite MushroomQuinoaChicken BrothChicken Breast
8Unit (g)100100100100100240100
9Servings4.20.0543.51.351.56.8
10Total (g)4205400350135360680
11
12Step 2: Importing Nutritional Info from Ingredients Tab
13Calories382.244.2160.077.0496.87.5680.0
14Fat (g)1.35.00.41.18.20.017.7
15Saturated Fat (g)0.40.70.20.40.90.04.1
16Monounsatured Fat (g)0.03.70.10.00.00.04.7
17Polyunsaturated Fat (g)0.00.50.10.00.00.02.9
18Cholesterol (mg)0.00.00.00.00.00.0496.4
19Total Carbohydrate (g)71.40.037.211.686.40.00.0
20
21Step 3: Sum of the Ingredients
22TypeUnitTotal
23Calories615.9
24Fat (g)g11.2
25Saturated Fat (g)g2.2
26Monounsatured Fat (g)g2.8
27Polyunsaturated Fat (g)g1.2
28Cholesterol (mg)mg165.5
29Total Carbohydrate (g)g68.9
30
Quinoa-BlackBeans
Cell Formulas
RangeFormula
C8C8=INDEX(tbl_Ingredients,MATCH($B8,tbl_Ingredients[Type],0),MATCH(C$7,tbl_Ingredients[#Headers],0))
D8:I8D8=INDEX(Ingredients!$B$4:$ZW$12,MATCH($B8,Ingredients!$B$4:$B$12,0),MATCH(D$7,Ingredients!$B$4:$ZX$4,0))
C10:I10C10=+C8*C9
C13:I19C13=INDEX(tbl_Ingredients,MATCH($B13,tbl_Ingredients[Type],0),MATCH(C$7,tbl_Ingredients[#Headers],0))*C$9
D23:D29D23=SUM(INDEX($B$13:$Z$19,MATCH($B23,$B$13:$B$19,0),0))/$C$4
 
Last edited:

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
See if this works for you:
  1. Replace all sheet name references which refer to the current sheet
    Replace 'Quinoa-BlackBeans'! with nothing in replace box
    When you build a formula referring to other sheets using the mouse, is also puts the sheet name for the cell references in the current workbook.
    These clutter up the formula making it look more complex and cause issues when you want to replicate the sheet using copy paste.

  2. Made the Ingredients table into an Excel Table
    This required having a heading for the first column, I have used type
    (you can change that later but if you want to copy paste my formulas you need to do it after replicating everything.
    Convert the data to a table
    Give it the table name tbl_ingredients
    (again you can change this at the very end)


Ingredients Sheet with Heading for Column 1 and converted to a Table called tbl_Ingredients

20210309 Cooking Ingredients lis.xlsx
ABCDEFGHIJ
1
2
3
4TypeBlack BeansQuinoaWhite MushroomOlive OilOnionChicken BrothChicken Breast
5Unit (g)100100100100100240100
6Calories9136822884405100
7Fat (g)0.36.10.31000.102.6
8Saturated Fat (g)0.10.70.1140.04200.6
9Monounsatured Fat (g)000730.01300.689
10Polyunsaturated Fat (g)00010.50.01700.424
11Cholesterol (mg)00000073
12Total Carbohydrate (g)17643.309.300
13
Ingredients


Quinoa-BlackBeans after formula modifications and refers to Ingredients Table Name - So create Ingredients Table and Table name before copying

20210309 Cooking Ingredients lis.xlsx
ABCDEFGHIJ
1
2
3
4Number of Meals3
5
6Step 1: Ingredient Entry
7Ingredients:Black BeansOlive OilOnionWhite MushroomQuinoaChicken BrothChicken Breast
8Unit (g)100100100100100240100
9Servings4.20.0543.51.351.56.8
10Total (g)4205400350135360680
11
12Step 2: Importing Nutritional Info from Ingredients Tab
13Calories382.244.2160.077.0496.87.5680.0
14Fat (g)1.35.00.41.18.20.017.7
15Saturated Fat (g)0.40.70.20.40.90.04.1
16Monounsatured Fat (g)0.03.70.10.00.00.04.7
17Polyunsaturated Fat (g)0.00.50.10.00.00.02.9
18Cholesterol (mg)0.00.00.00.00.00.0496.4
19Total Carbohydrate (g)71.40.037.211.686.40.00.0
20
21Step 3: Sum of the Ingredients
22TypeUnitTotal
23Calories615.9
24Fat (g)g11.2
25Saturated Fat (g)g2.2
26Monounsatured Fat (g)g2.8
27Polyunsaturated Fat (g)g1.2
28Cholesterol (mg)mg165.5
29Total Carbohydrate (g)g68.9
30
Quinoa-BlackBeans
Cell Formulas
RangeFormula
C8C8=INDEX(tbl_Ingredients,MATCH($B8,tbl_Ingredients[Type],0),MATCH(C$7,tbl_Ingredients[#Headers],0))
D8:I8D8=INDEX(Ingredients!$B$4:$ZW$12,MATCH($B8,Ingredients!$B$4:$B$12,0),MATCH(D$7,Ingredients!$B$4:$ZX$4,0))
C10:I10C10=+C8*C9
C13:I19C13=INDEX(tbl_Ingredients,MATCH($B13,tbl_Ingredients[Type],0),MATCH(C$7,tbl_Ingredients[#Headers],0))*C$9
D23:D29D23=SUM(INDEX($B$13:$Z$19,MATCH($B23,$B$13:$B$19,0),0))/$C$4

My edit time on the above expired.
  • Take a backup copy of your spreadsheet first
  • Do not copy in the ingredients sheet, do steps 1 & 2 first and that should take care of the ingredients changes
    (the xl2bb function doesn't handle copying in tables as far as I can tell)
  • Copy in the Quinoa-BlackBeans sheet
  • Note: I have left in the hard coding of Column Z as the maximum no of columns in the recipes sheet. An alternative was to just sum the whole row, which would be a bit inefficient but probably less so than using Offset which I have removed. But if you are happy to limit it to Z just leave it.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
My edit time on the above expired.
  • Take a backup copy of your spreadsheet first
  • Do not copy in the ingredients sheet, do steps 1 & 2 first and that should take care of the ingredients changes
    (the xl2bb function doesn't handle copying in tables as far as I can tell)
  • Copy in the Quinoa-BlackBeans sheet
  • Note: I have left in the hard coding of Column Z as the maximum no of columns in the recipes sheet. An alternative was to just sum the whole row, which would be a bit inefficient but probably less so than using Offset which I have removed. But if you are happy to limit it to Z just leave it.

You will also need to copy C8 to D8-I8
 

shadowhound

New Member
Joined
Mar 8, 2021
Messages
4
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Thank you Alex. I've learned at least two new things from your post, namely the 1) cleaner sheet name references and 2) far cleaner dynamic sum (without the offsets). I'm very happy with the dynamic sum, the offset function is clunky as hell.

The data table formulas did not work. I followed your instructions 1) adding "Type" to Cell B4 2) converted B4:I12 to table "tbl_Ingredients" (and would successfully ping it through F5), but Excel warns me that there's a formula error when I try to copy your formula into cell C8. I am using Excel 2010, so its possible that there has been a change in taxonomy since then.

Separately, I'm trying to get somewhere which is maybe too ambitious, and that is to go from Step 1 to Step 3 while skipping Step 2. To have the formula in Step 3 import all of the ingredients from Step 1, multiply them by the number of servings in Step 1, and output the sum in Step 3. Is that possible?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
Thank you Alex. I've learned at least two new things from your post, namely the 1) cleaner sheet name references and 2) far cleaner dynamic sum (without the offsets). I'm very happy with the dynamic sum, the offset function is clunky as hell.

The data table formulas did not work. I followed your instructions 1) adding "Type" to Cell B4 2) converted B4:I12 to table "tbl_Ingredients" (and would successfully ping it through F5), but Excel warns me that there's a formula error when I try to copy your formula into cell C8. I am using Excel 2010, so its possible that there has been a change in taxonomy since then.

Separately, I'm trying to get somewhere which is maybe too ambitious, and that is to go from Step 1 to Step 3 while skipping Step 2. To have the formula in Step 3 import all of the ingredients from Step 1, multiply them by the number of servings in Step 1, and output the sum in Step 3. Is that possible?

1) Table Formulas.
Try this.
in C8, highlight tbl_Ingredients and then go to the Ingredients sheet and highlight the whole table.
If this works the table formula works in principle. Then check if the spelling is identical on the table names and field names, in the rest of the formula.

1615443735456.png

2) Bypassing Step 2
I am not a bit fan of doing this sort of thing in one step but I tried to see if I could make is work.

I got as far as using Sumproduct to pull in all the right figures for Calories but then realised they individually needed to be multiplied by row 9 being the serving number. Since this array is a different size to the Ingredients array, I got stuck there. I am sure there are people answering questions on the forum that could make it work.
Just keep in mind that you are going to have to work with the resulting formula and just the first part looks like this. (D23 just the calory total, still needs multiply by C9:I9 / C4)
Excel Formula:
=SUMPRODUCT(ISNUMBER(MATCH(tbl_Ingredients[[#Headers],[Black Beans]:[Chicken Breast]],$C$7:$I$7,0))*INDEX(tbl_Ingredients[[Black Beans]:[Chicken Breast]],MATCH(B23,tbl_Ingredients[Type],0),0))
 
Solution

shadowhound

New Member
Joined
Mar 8, 2021
Messages
4
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

1) Table Formulas.
Try this.
in C8, highlight tbl_Ingredients and then go to the Ingredients sheet and highlight the whole table.
If this works the table formula works in principle. Then check if the spelling is identical on the table names and field names, in the rest of the formula.

View attachment 34086
2) Bypassing Step 2
I am not a bit fan of doing this sort of thing in one step but I tried to see if I could make is work.

I got as far as using Sumproduct to pull in all the right figures for Calories but then realised they individually needed to be multiplied by row 9 being the serving number. Since this array is a different size to the Ingredients array, I got stuck there. I am sure there are people answering questions on the forum that could make it work.
Just keep in mind that you are going to have to work with the resulting formula and just the first part looks like this. (D23 just the calory total, still needs multiply by C9:I9 / C4)
Excel Formula:
=SUMPRODUCT(ISNUMBER(MATCH(tbl_Ingredients[[#Headers],[Black Beans]:[Chicken Breast]],$C$7:$I$7,0))*INDEX(tbl_Ingredients[[Black Beans]:[Chicken Breast]],MATCH(B23,tbl_Ingredients[Type],0),0))
1) I made a mistake. I made a named range "tbl_ingredients" that was referring to "Table#1", which is why I was getting an error. It works now and I'll be using this going forward.

2) You're right, this seems more trouble than it's worth.

Thank you vm for your help Alex. I learned a few new tricks.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
1) I made a mistake. I made a named range "tbl_ingredients" that was referring to "Table#1", which is why I was getting an error. It works now and I'll be using this going forward.

2) You're right, this seems more trouble than it's worth.

Thank you vm for your help Alex. I learned a few new tricks.

You mentioned using Excel 2010 but your profile indicated you also have Office 365.
Do you have both ? Any reason you are using Excel 2010 if you do ?
In terms of learning, 365 has a lot of new functions you are missing out on by not using it.

PS: Tables are great for using in Pivot Tables and Lookup type formulas (including SUMIFS) because they autoexpand and require less formula maintenance. Structured referencing means columns can change positions without breaking the formulas referring to them. They also autofill down any formulas inside the tables.
 
Last edited:

shadowhound

New Member
Joined
Mar 8, 2021
Messages
4
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I have a Office 365 license from my former college, but worry it will get yanked eventually. The Office 2010 license is mine, and it works well enough for my fairly basic needs so far.

Tables are new to me, and seem to be pretty elegant if there's new data entered in columns/rows. I've previously made very wide ranges in my formulas.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
I have a Office 365 license from my former college, but worry it will get yanked eventually. The Office 2010 license is mine, and it works well enough for my fairly basic needs so far.

Tables are new to me, and seem to be pretty elegant if there's new data entered in columns/rows. I've previously made very wide ranges in my formulas.

Here are some options 365 upgrade options you could consider:-

Zero or little cost:-
1) Check with your employer to see if they subscribe to the Employee Home Use Program.
If they do you would download 365 at little or no cost using your work email address.
(you could probably even just try it using your work email address but I haven't googled where to get it the first time for the instal)

2) See if a friend or family member subscribes to Microsoft 365 "Family". They can invite up to 5 family or friends to use their subscription (you are only sharing use of the product they can't access anything you do with it) - zero cost

3) Ask a friend or family member to upgrade from a Personal to Family subscription and maybe contribute to the small incremental cost.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,436
Messages
5,636,282
Members
416,910
Latest member
zezspecs

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