Sum result of Index and Match with multiple criterias

[Vince1199s]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Food Item	Weight (grams)	Energy Kj	Cal	Fat	Saturated	Carbs	Sugars	Fibre	Protein	Salt	Size	Protein %	Total Cal	Total Fat	Total Sat Fat	Total Carbs	Total Fibre	Total Protein
2	Bread Roll	90.00 g	884 Kj	153 Cal	3.78 g	1.44 g	0.45 g	0.45 g	0.45 g	29.16 g	0.18 g	1.00	19%	153	3.78	1.44	0.45	0.45	29.16
3	Chicken Fillets	100.00 g	597 Kj	141 kcal	1.70 g	0.40 g	0.50 g	0.50 g	0.50 g	30.70 g	0.13 g	1.00	22%	141	1.7	0.4	0.5	0.5	30.7
4	Fuel10K	1.00 g	925 Kj	221 kcal	2.60 g	0.50 g	37.50 g	16.90 g	2.40 g	11.50 g	0.11 g	1.00	5%	221	2.6	0.5	37.5	2.4	11.5
5	Ham	1.00 g	58 Kj	14 Cal	0.45 g	0.15 g	0.50 g	0.50 g	0.50 g	2.25 g	0.23 g	1.00	17%	13.5	0.45	0.15	0.5	0.5	2.25
6	Jacket Potato	1.00 g	914 Kj	216 kcal	0.50 g	0.10 g	45.20 g	2.80 g	5.20 g	5.00 g	0.01 g	1.00	2%	216	0.5	0.1	45.2	5.2	5
7	Rice Microwave	1.00 g	1692 Kj	400 kcal	4.24 g	1.00 g	80.00 g	4.16 g	5.36 g	8.80 g	1.33 g	1.00	2%	400	4.24	1	80	5.36	8.8
8	Tuna Spring Water	100.00 g	478 Kj	113 kcal	0.50 g	0.10 g	0.50 g	0.50 g	0.50 g	16.20 g	0.45 g	3.40	14%	384.2	1.7	0.34	1.7	1.7	55.08
9	Whey Protein	25.00 g	435 Kj	103 kcal	1.90 g	1.30 g	1.00 g	1.00 g	0.00 g	21.00 g	0.13 g	1.00	20%	103	1.9	1.3	1	0	21
10	0.00	0.00 g	0 Kj	0 kcal	0.00 g	0.00 g	0.00 g	0.00 g	0.00 g	0.00 g	0.00 g	0.00		0	0	0	0	0	0
Food

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8	Item 9	Item 10	Item 11	Item 12	Total Carbs	Total Fibre	Total Protein	Total Cal	Total Fat	Total Sat Fat
2	11/04/2016	Fuel10K	Whey Protein	Bread Roll	Ham	Chicken Fillets	Rice Microwave	Jacket Potato	Tuna Spring Water					37.5	2.4	11.5	221	2.6	0.5
Diary

 

[Andrew Poulsom]

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.

 

[pgc01]

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

 

[pgc01]

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

 

[Vince1199s]

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

 

[Vince1199s]

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.

 

[oriwitt]

I'm gonna use this for my intake requirements.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[Andrew Poulsom]

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.

 

[Vince1199s]

No but I think my first formula was an array