» 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:
Recommended Books:
- The Fall of Advertising and the Rise of PR
- Business Analysis with Microsoft Excel (2nd Edition)
- Real Estate Finance and Investments (Real Estate Finance and Investments, 11th Ed)
- Microsoft Access 2002 for Dummies
- Accounting for Dummies
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
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))