Example:
I have a sheet listing the number of calories for various foods.
In another sheet I have a list of some of those foods to be used in a meal, and would like to calculate the total number of calories for that particular meal.
How can I lookup the calorie data for each food item in the meal, and then sum each of those values together?
I thought something like =SUM(VLOOKUP(A2:A4,$Sheet1.A2:B13,2,0)) might work but alas not.
Sum a SUMIFS:
=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5))
This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.
Use the B column to grab your calorie count with vlookup, then sum those at the bottom of the column.
B cells next to meal consumed:
=vlookup(A2,$Sheet1!A:B,2,0)
Then a simple sum of the column:
=sum(B2:B5)
Your function above may work as an array function by using cntrl-shift-enter to finalize it and changing your period to an exclamation mark. (untested)
In my version of Excel, SUM() works just fine (Version 2010 (Build 13328.20292 Click-to-Run)).
However, and I cannot test this, that may ONLY be due to this version having the SPILL functionality.
But... that just would mean you need {CSE} or a function that assumes an array, like SUMPRODUCT() (which other answers rightfully use). However...
When I use it, I have to use an array for the second factor in it. Can't use a simple 1 or even a {1}, since VLOOKUP() (which does NOT deserve being maligned for ANYTHING other than not "looking left") is using an input array. Various ways to build such an array, including simply typing one in {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20} perhaps to handle as many as 20 ingredients. Or our old friend ROW(1:LEN()) adapted to the circumstances with, perhaps, COUNTA() instead of LEN(). Except...
It seems my version doesn't like non-SPILL arrays, arrays produced by non-SPILL techniques. So it fails. But one can coerce the situation by using a SPILL function, one in fact built specifically for SPILLing.
That is SEQUENCE(). I used the following for a simple version of the above so the cell addresses would need adjusting, but otherwise, it's production ready:
=SUMPRODUCT(VLOOKUP(D1:D3,A1:B3,2,FALSE), SEQUENCE(COUNTA(D1:D3),1,1,0) )
(Source table being A1:B3, items in dish being D1:D3.)
SEQUENCE() forces SUMPRODUCT() back to handling arrays instead of taking the traditional "first cell in the array ("upper left")" as its only input. The inputs are in a column, so COUNTA() comes first, then ",1" and not the usual other way around. You want only 1's in the array, so the start value is the next 1, and you want ONLY 1's so the last parameter, step value, is 0.
Side note: Always liked VLOOKUP()'s ability to use an array. Never liked that it could only be "One VLOOKUP, One Array" (the motto of the Texas Array-ngers...). (So no array as an input generating several arrays as outputs: Like here, except maybe the source data included "carbs, protein, fat, fiber, yada, yada, yada" too and one wanted their totals also, all from (and now with LET(), "in" too) the single function. Thought about that as I wrote this answer and it occurs to me that the underlying reason for that, whatever it might be, might be at the root of why nice new functions like XLOOOKUP() can't produce 2-D output (much less 3-D or 4-D). Hope not, 'cause if the reason here is the reason there, it ain't happening soon that they fix it!
