Formula to look up list of values in a range and then sum associated values

[dommeehan]

Hi I'd like a formula in B12:H12 that assesses all the food eaten over the course of the day and then matches within the reference table (N2:P12) and returns the sum of all the protein consumed. For example, B12 would show 103. I then need a similar formula for calories so B13 would show 885.

Food.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday						Food	Protein	Calories
3	Breakfast	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg						Boiled Egg	6	60
4		Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt						Yoghurt	18	110
5	Lunch	Tuna	Prawns	Tuna	Prawns	Tuna	Prawns	Tuna						Tuna	25	100
6		Kale	Kale	Kale	Kale	Kale	Kale	Kale						Kale	3	20
7		Nuts	Nuts	Nuts	Nuts	Nuts	Nuts	Nuts						Nuts	12	150
8	Dinner	Chicken	Salmon	Salmon	Chicken	Salmon	Chicken	Chicken						Chicken	30	180
9		Broccoli	Broccoli	Broccoli	Broccoli	Broccoli	Broccoli	Broccoli						Broccoli	5	25
10		Potato	Potato	Potato	Potato	Potato	Potato	Potato						Potato	4	240
11														Prawns	15	170
12	Protein													Salmon	24	220
13	Calories
Schedule

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday						Food	Protein	Calories
3	Breakfast	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg						Boiled Egg	6	60
4		Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt						Yoghurt	18	110
5	Lunch	Tuna	Prawns	Tuna	Prawns	Tuna	Prawns	Tuna						Tuna	25	100
6		Kale	Kale	Kale	Kale	Kale	Kale	Kale						Kale	3	20
7		Nuts	Nuts	Nuts	Nuts	Nuts	Nuts	Nuts						Nuts	12	150
8	Dinner	Chicken	Salmon	Salmon	Chicken	Salmon	Chicken	Chicken						Chicken	30	180
9		Broccoli	Broccoli	Broccoli	Broccoli	Broccoli	Broccoli	Broccoli						Broccoli	5	25
10		Potato	Potato	Potato	Potato	Potato	Potato	Potato						Potato	4	240
11														Prawns	15	170
12	Protein	103	87	97	93	97	93	103						Salmon	24	220
13	Calories	885	995	925	955	925	955	885
Holder

Cell Formulas
Range	Formula
B12:H12	B12	=SUM(FILTER($O$3:$O$12,COUNTIF(B3:B10,$N$3:$N$12)))
B13:H13	B13	=SUM(FILTER($P$3:$P$12,COUNTIF(B3:B10,$N$3:$N$12)))

 

[dommeehan]

This is great, thanks! Is there anyway to make that formula multiple proof? E.g. in the below example, some items appear twice and the formula you suggested doesn't account for that. Thanks for any help in advance.

Food.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday						Food	Protein	Calories
3	Breakfast	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg	Boiled Egg						Boiled Egg	6	60
4		Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt	Yoghurt						Yoghurt	18	110
5	Lunch	Tuna	Prawns	Tuna	Prawns	Tuna	Prawns	Tuna						Tuna	25	100
6		Kale	Kale	Kale	Kale	Kale	Kale	Kale						Kale	3	20
7		Nuts	Nuts	Nuts	Nuts	Nuts	Nuts	Nuts						Nuts	12	150
8	Dinner	Chicken	Salmon	Salmon	Chicken	Salmon	Chicken	Chicken						Chicken	30	180
9		Broccoli	Broccoli	Broccoli	Broccoli	Broccoli	Broccoli	Broccoli						Broccoli	5	25
10		Potato	Potato	Potato	Potato	Potato	Potato	Potato						Potato	4	240
11	Snack	Boiled Egg												Prawns	15	170
12		Yoghurt												Salmon	24	220
13
14	Protein
15	Calories
Schedule

 

[Fluff]

Surely you would still want to add them. After all if you have eaten 2 boiled eggs that's 120 calories not 60.

 

[dommeehan]

Correct, but when I use the formula you suggested it doesn't add the protein / calorie amount twice it just counts it as one, irrespective of how many times it appears.

 

[Fluff]

Ok, how about

=SUM(SUMIFS($O$3:$O$12,$N$3:$N$12,B3:B12))

 

[dommeehan]

Ok, how about

=SUM(SUMIFS($O$3:$O$12,$N$3:$N$12,B3:B12))

This is perfect, thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.