» Summing Values from Two Ranges, Based on Two Criteria
CATEGORY - Summing
VERSION - All Microsoft Excel Versions
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)
Book Store:
Recommended Books:
- Rich Dad's Guide to Investing: What the Rich Invest in, That the Poor and the Middle Class Do Not!
- Accounting the Easy Way
- Writing Excel Macros with VBA, 2nd Edition
- Seven Habits Of Highly Effective People
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Special Edition Using Microsoft Outlook 2002
No comments have been submitted.
