CSE formula with index/match

[Faller]

I am trying to index/match to find numbers based off of words in an array formula but when I do the match part always returns the first number it sees. So I'm only effectively adding a count multiplier.

My formula is:
{=SUM(IF(B2:J2="",0,INDEX(Food:Calories,MATCH(Calendar!B2:J2,Food,0),2)))}

I'm trying to type in what I eat in a day and have it spit out how many calories I've eaten in a day. But it always pulls from the first thing I've eaten that day and multiplies it by how many entries I put in for the day.

Thank you

 

[GlennUK]

The contents of B2 to J2 must contain only items that match cells in the Food area ( and I mean no blank cells either ). Double check that.

Example of using dynamic range definition for Calendar:

Excel Workbook
	B	C	D	E	F
2	More	Apple	Lemonade
3	320
4
5				CalAndDummy	=Values!$B$2:$B$13
6				Calendar	=OFFSET(Sheet1!$B$2,0,0,1,COUNTA(Sheet1!$2:$2))
7				Calories	=Values!$B$2:$B$12
8				Food	=Values!$A$2:$A$12
9				FoodAndTitle	=Values!$A$1:$A$12
Sheet1

 

[Faller]

Worked like a charm. Just put an X for blanks and made it 0 calories.
I have no idea how this formula works but I guess I now have some learning to do.

Thank you kindly
Vince

 

[GlennUK]

Worked like a charm. Just put an X for blanks and made it 0 calories.
I have no idea how this formula works but I guess I now have some learning to do.

Thank you kindly
Vince

That's one way to get it working. I've amended my last entry to show how to do it with a dynamically defined named range that calculates how many entries to use, which may be of use.

Good luck!

 

[GlennUK]

Also, here's a breakdown of the formula to help you with your learning:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Food	Calories		Calendar				MATCH(D2:D5,FoodAndTitle,0)	ROW(Calories)	FREQUENCY(MATCH(D2:D5,FoodAndTitle,0),ROW(Calories))	CalAndDummy	Multiply
2	Mush	100		More				5	2	0	100	0
3	Stuff	101		Apple				9	3	0	101	0
4	Whatever	102		Lemonade				12	4	0	102	0
5	More	103		Apple				9	5	1	103	103
6	Less	104							6	0	104	0
7	Dunno	105							7	0	105	0
8	Donuts	106							8	0	106	0
9	Apple	107							9	2	107	214
10	Banana	108							10	0	108	0
11	Melon	109							11	0	109	0
12	Lemonade	110		Result					12	1	110	110
13	dummy	0		427						0	0	0
14											Final result ========>>>	427
Values