Sum result of Index and Match with multiple criterias

Vince1199s

New Member
Joined
Mar 4, 2016
Messages
22
I have a food diary, in sheet 1 (Food) is a list of foods with their relative carb, fat, fibre & protein Values.

In Sheet 2 Diary I will enter what I have eaten that day and I need this sheet to give me the total carb content for the various foods I have eaten that day, How do I do this ?

In the Sheet Diary in Cell N2 I have this =INDEX(Food!$B$2:$S$110,MATCH($B$2,Food!$A$2:$A$110,0),MATCH(N$1,Food!$B$1:$S$1,0)) I thought it would be simple just to add a + sign then INDEX(Food!$B$2:$S$110,MATCH($C$2,Food!$A$2:$A$110,0),MATCH(N$1,Food!$B$1:$S$1,0)) But it does not like it and gives #REF!
What am I missing ?


Excel 2010
ABCDEFGHIJKLMNOPQRS
1Food ItemWeight (grams)Energy KjCalFatSaturatedCarbsSugarsFibreProteinSaltSizeProtein %Total CalTotal FatTotal Sat FatTotal CarbsTotal FibreTotal Protein
2Bread Roll90.00 g884 Kj153 Cal3.78 g1.44 g0.45 g0.45 g0.45 g29.16 g0.18 g1.0019%1533.781.440.450.4529.16
3Chicken Fillets100.00 g597 Kj141 kcal1.70 g0.40 g0.50 g0.50 g0.50 g30.70 g0.13 g1.0022%1411.70.40.50.530.7
4Fuel10K1.00 g925 Kj221 kcal2.60 g0.50 g37.50 g16.90 g2.40 g11.50 g0.11 g1.005%2212.60.537.52.411.5
5Ham1.00 g58 Kj14 Cal0.45 g0.15 g0.50 g0.50 g0.50 g2.25 g0.23 g1.0017%13.50.450.150.50.52.25
6Jacket Potato1.00 g914 Kj216 kcal0.50 g0.10 g45.20 g2.80 g5.20 g5.00 g0.01 g1.002%2160.50.145.25.25
7Rice Microwave1.00 g1692 Kj400 kcal4.24 g1.00 g80.00 g4.16 g5.36 g8.80 g1.33 g1.002%4004.241805.368.8
8Tuna Spring Water100.00 g478 Kj113 kcal0.50 g0.10 g0.50 g0.50 g0.50 g16.20 g0.45 g3.4014%384.21.70.341.71.755.08
9Whey Protein25.00 g435 Kj103 kcal1.90 g1.30 g1.00 g1.00 g0.00 g21.00 g0.13 g1.0020%1031.91.31021
100.000.00 g0 Kj0 kcal0.00 g0.00 g0.00 g0.00 g0.00 g0.00 g0.00 g0.00000000
Food



Excel 2010
ABCDEFGHIJKLMNOPQRS
1DateItem 1Item 2Item 3Item 4Item 5Item 6Item 7Item 8Item 9Item 10Item 11Item 12Total CarbsTotal FibreTotal ProteinTotal CalTotal FatTotal Sat Fat
211/04/2016Fuel10KWhey ProteinBread RollHamChicken FilletsRice MicrowaveJacket PotatoTuna Spring Water37.52.411.52212.60.5
Diary
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Your formula works for me:

=INDEX(Food!$B$2:$S$110,MATCH($B$2,Food!$A$2:$A$110,0),MATCH(N$1,Food!$B$1:$S$1,0))+INDEX(Food!$B$2:$S$110,MATCH($C$2,Food!$A$2:$A$110,0),MATCH(N$1,Food!$B$1:$S$1,0))

returning 38.5, so check again that you entered it correctly. If it returns #REF! one of the values returned by MATCH is outside the INDEXED range.
 
Upvote 0
Hi

Try in Diary!N2:

=SUM(IF(Food!$A$2:$A$9=$B$2:$M$2,INDEX(Food!$B$2:$S$9,0,MATCH(N1,Food!$B$1:$S$1,0))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Copy across
 
Last edited:
Upvote 0
... or the non-array formula

=SUMPRODUCT(SUMIFS(INDEX(Food!$B$2:$S$9,0,MATCH(N1,Food!$B$1:$S$1,0)),Food!$A$2:$A$9,$B$2:$M$2))
 
Upvote 0
Hi Andrew,

Yes I have now use CTRL-SHIFT-ENTER and it has worked thank you.
But on a side note I have used an array formula before in other sheets and have not used the method CTRL-SHIFT-ENTER and it has been fine so I am really confused as to why it did not work in this instance
 
Upvote 0
PGC01,

Thank you for the non array formula that is actually a lot simpler than the one I was trying to use and takes less editing.
 
Upvote 0
Hi Andrew,

Yes I have now use CTRL-SHIFT-ENTER and it has worked thank you.
But on a side note I have used an array formula before in other sheets and have not used the method CTRL-SHIFT-ENTER and it has been fine so I am really confused as to why it did not work in this instance

What I posted wasn't an array formula.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top