Looking to combine import using indexmatch with sumif

shadowhound · Mar 8, 2021

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

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

240

100

9

Servings

4.2

0.05

4

3.5

1.35

1.5

6.8

10

Total (g)

11

12

Step 2: Importing Nutritional Info from Ingredients Tab

13

Calories

14

Fat (g)

15

Saturated Fat (g)

16

Monounsatured Fat (g)

17

Polyunsaturated Fat (g)

18

Cholesterol (mg)

0.0

496.4

19

Total Carbohydrate (g)

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

Quinoa-BlackBeans

Cell Formulas
Range	Formula
C8:I8	C8	=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:I10	C10	=+C8*C9
C13:H19	C13	=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:I19	I13	=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: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

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.6

9

Monounsatured Fat (g)

0

73

0.0

0.7

10

Polyunsaturated Fat (g)

0

10.5

0.0

0.4

11

Cholesterol (mg)

0

73

12

Total Carbohydrate (g)

17.0

64

3.3

0

9.3

0

Ingredients

Alex Blakenburg · Mar 9, 2021

See if this works for you:

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.
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

A

B

C

D

E

F

G

H

I

J

1

2

3

4

Type

Black Beans

Quinoa

White Mushroom

Olive Oil

Onion

Chicken Broth

Chicken Breast

5

Unit (g)

100

240

100

6

Calories

91

368

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.042

0

0.6

9

Monounsatured Fat (g)

0

73

0.013

0

0.689

10

Polyunsaturated Fat (g)

0

10.5

0.017

0

0.424

11

Cholesterol (mg)

0

73

12

Total Carbohydrate (g)

17

64

3.3

0

9.3

0

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

A

B

C

D

E

F

G

H

I

J

1

2

3

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

240

100

9

Servings

4.2

0.05

4

3.5

1.35

1.5

6.8

10

Total (g)

11

12

Step 2: Importing Nutritional Info from Ingredients Tab

13

Calories

14

Fat (g)

15

Saturated Fat (g)

16

Monounsatured Fat (g)

17

Polyunsaturated Fat (g)

18

Cholesterol (mg)

0.0

496.4

19

Total Carbohydrate (g)

20

21

Step 3: Sum of the Ingredients

22

Type

Unit

Total

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

30

Quinoa-BlackBeans

Cell Formulas
Range	Formula
C8	C8	=INDEX(tbl_Ingredients,MATCH($B8,tbl_Ingredients[Type],0),MATCH(C$7,tbl_Ingredients[#Headers],0))
D8:I8	D8	=INDEX(Ingredients!$B$4:$ZW$12,MATCH($B8,Ingredients!$B$4:$B$12,0),MATCH(D$7,Ingredients!$B$4:$ZX$4,0))
C10:I10	C10	=+C8*C9
C13:I19	C13	=INDEX(tbl_Ingredients,MATCH($B13,tbl_Ingredients[Type],0),MATCH(C$7,tbl_Ingredients[#Headers],0))*C$9
D23:D29	D23	=SUM(INDEX($B$13:$Z$19,MATCH($B23,$B$13:$B$19,0),0))/$C$4

Alex Blakenburg · Mar 9, 2021

Alex Blakenburg said:
See if this works for you:

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.

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
A B C D E F G H I J
1
2
3
4 Type 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 368 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.042 0 0.6
9 Monounsatured Fat (g) 0 0 0 73 0.013 0 0.689
10 Polyunsaturated Fat (g) 0 0 0 10.5 0.017 0 0.424
11 Cholesterol (mg) 0 0 0 0 0 0 73
12 Total Carbohydrate (g) 17 64 3.3 0 9.3 0 0
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
A B C D E F G H I J
1
2
3
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 Type Unit Total
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
30
Quinoa-BlackBeans
Cell Formulas
Range Formula
C8 C8 =INDEX(tbl_Ingredients,MATCH($B8,tbl_Ingredients[Type],0),MATCH(C$7,tbl_Ingredients[#Headers],0))
D8:I8 D8 =INDEX(Ingredients!$B$4:$ZW$12,MATCH($B8,Ingredients!$B$4:$B$12,0),MATCH(D$7,Ingredients!$B$4:$ZX$4,0))
C10:I10 C10 =+C8*C9
C13:I19 C13 =INDEX(tbl_Ingredients,MATCH($B13,tbl_Ingredients[Type],0),MATCH(C$7,tbl_Ingredients[#Headers],0))*C$9
D23:D29 D23 =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 · Mar 9, 2021

Alex Blakenburg said:
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 · Mar 10, 2021

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 · Mar 11, 2021

shadowhound said:
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.

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))

shadowhound · Mar 11, 2021

Alex Blakenburg said:
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 · Mar 11, 2021

shadowhound said:
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.

shadowhound · Mar 11, 2021

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 · Mar 11, 2021

shadowhound said:
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.

Looking to combine import using indexmatch with sumif

shadowhound

New Member

Alex Blakenburg

MrExcel MVP

Alex Blakenburg

MrExcel MVP

Alex Blakenburg

MrExcel MVP

shadowhound

New Member

Alex Blakenburg

MrExcel MVP

shadowhound

New Member

Alex Blakenburg

MrExcel MVP

shadowhound

New Member

Alex Blakenburg

MrExcel MVP

Similar threads

Share this page

Looking to combine import using indexmatch with sumif

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock