How to use SUMIFS and SUMPRODUCT function **with a multiplier column** for summing values if criteria range exists in a range?

Question

This question refers to this Stack Overflow question.

This is the original question:

A   B    C 
a   3    d 
b   1    a 
c   8    e 
d   5    

I want to use SUMIFS function, sum range is B1:B4, if the corresponding value in column A exists in column C, sum the values in column B, in this case the sum will be 3 + 5 = 8.

The answer turned out to be:

=SUMPRODUCT(SUMIF(A1:A4,C1:C3,B1:B4)) 

---

I would like to create the same result, but with a multiplier on one of the ranges.

Here's my question:

A   B   C   D
a   3   d   3
b   1   a   1
c   8   e   1
d   5   d   3

The only thing I would like to do differently is add a range D1:D3 that acts as a multiplier. In this case, the sum I want would be (3x3)+(5x3)= 24.

One challenge is that the ranges are different sizes in the "Test Data" google sheet linked below. The "Test Data" sheet is an example of how I would like to total daily calories based on the number of servings of items that I eat as dictated by the "multiplier column."

I would like the numbers in C2:C27 to be a multiplier and factored into the total in D36.

For example, if I ate 2 servings of Beef, then 2 servings worth of Beef's ​calories would be displayed in the daily totals below the data. Currently, using the formula =SUMPRODUCT(SUMIF(Meal_Items!$A$2:$A$100,$D$2:$D$28,Meal_Items!$B$2:$B$100)), only 1 serving of beef is weighted.

Test Data

See linked Test Data set above

Answer 1

The original question could also be solved with this formula

=SUMPRODUCT((COUNTIF(C1:C3,A1:A4)>0)+0,B1:B4)

using that version it's a simple matter to add one or more multiplier columns

=SUMPRODUCT((COUNTIF(C1:C3,A1:A4)>0)+0,B1:B4,D1:D4)

Given discussion in comments this formula should do what you want in either Excel or google sheets

=SUMPRODUCT(IFERROR(LOOKUP($D$2:$D$28,Meal_Items!$A$2:$A$100‌​,Meal_Items!$B$2:$B$‌​100),0),$C$2:$C$28)