Google Sheets: How can I SUM total from drop down menu

Question

I am making a sheet for meal planning and tracking calories.

I have made a separate sheet with the recipe names and their calories. In the main meal prep section I have 4 drop downs per day where I can pick the recipe names. I want to take those names, reference the calories and put the sum total at the bottom for each day. I do think SUMIF is the right direction but I can't seem to get it to work.

https://docs.google.com/spreadsheets/d/1wiFmKy0it96hkUKxL5QcyGYG8xtSl7E2fUEdEF34ajo/edit?usp=sharing

Here is the file so you can see what I am making. Thank you for taking the time to read this and help.

Thank you,

DemonEarth

Answer 1

try:

=ARRAYFORMULA(TRANSPOSE(MMULT(TRANSPOSE(
 IFNA(VLOOKUP(B2:H5, Meals!A:B, 2, 0), 0)), 
 SEQUENCE(ROWS(B2:H5), 1, 1, 0))))

or:

=INDEX(QUERY(IFNA(VLOOKUP(B2:H5, Meals!A:B, 2, 0), 0), 
 "select sum(Col1),sum(Col2),sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7)"), 2)

=INDEX(QUERY(IFNA(VLOOKUP(B2:H5, Meals!A:B, 2, ), 0), 
 "select "&TEXTJOIN(",", 1, "sum(Col"&SEQUENCE(COLUMNS(B2:H5))&")", )), 2)