» Summing Values from Two Ranges, Based on Two Criteria
CATEGORY - Summing
VERSION - All Microsoft Excel Versions
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)
Book Store:
Summing Values from Two Ranges, Based on Two Criteria
sid
Will someone please tell me how I read this command, ie what the instructions "say". I don't understand the "F2" and "F3" symbols. Thanks.
<frogfund wrote on April 17, 2006 19:39 EST
The following formula is a little simpler and will also work
=SUM((A2:A7=F2)*(B2:B7),(C2:C7=F3)*(D2:D7)) >
=SUM((A2:A7=F2)*(B2:B7),(C2:C7=F3)*(D2:D7))