I have a sheet listing the number of calories for various foods.

Calorie Data

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.

  • 3
    Do an individual VLOOKUP for each cell in the meal table, then use sum for the total. – fixer1234 Jan 29 at 0:11
  • Try to use SUMPRODUCT function. – Lee Jan 29 at 8:56
  • Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile. – Michthan Jan 29 at 14:38



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:


Then a simple sum of the column:


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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.