» 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:
- Microsoft Office Xp: Advanced Concepts and Techniques: Word 2002, Excel 2002, Access 2002, Powerpoint 2002
- Microsoft Windows XP Inside Out
- H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond
- Special Edition Using Microsoft Office XP
- The 22 Immutable Laws of Branding
- How to Use Financial Statements: A Guide to Understanding the Numbers
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))