» Summing Values from Two Ranges, Based on Two Criteria

Problem:

Columns A & B contain a list of candy eaten last week and the calories corresponding with each item.
Columns C & D contain the equivalent information regarding fruit and vegetables.
We want a total of the calories gained last week by eating the food items specified in column F.

Solution 1:

Use the SUMPRODUCT function as shown in either of the following formulas:
=SUMPRODUCT((A2:A7=F2)*(B2:B7))+SUMPRODUCT((C2:C7=F3)*(D2:D7))
=SUMPRODUCT(--(A2:A7=F2),(B2:B7))+SUMPRODUCT(--(C2:C7=F3),(D2:D7))

Solution 2:

Use the SUMIF function as shown in the following formula:
=SUMIF(A2:A7,F2,B2:B7)+SUMIF(C2:C7,F3,D2:D7)
Screenshot // Summing Values from Two Ranges, Based on Two Criteria