» Performing calculations based on multiple criteria, using SUMPRODUCT function
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Calculating the total sum of discounts made.
A discount is available when the value in the discount column (F) = ""YES"" and
only for prices higher than 2000.
Solution:
To find the discount on the price in cell B7, according to the above criteria,
use the following formula:
=SUMPRODUCT(((B2:B5=E2)*(A2:A5>E3)),C2:C5/100)
Example:
Price_____Discount______Percentage
2500______YES___________20
1500______NO____________30
500_______YES___________10
3000______YES__________ 10
Result:___30.00%
Book Store:
Recommended Books:
- The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
- Personal Finance for Dummies
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Yes, You Can Time the Market!
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- Absolute Beginner's Guide to Microsoft Excel 2002
Conditional Sum Wizard
Highlight the data to be worked on including field names
Tools...Conditional Sum
Walk through the Wizard as shown below in jpg format (as example)
This creates an array formula for you
Good Luck