» 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:
- Microsoft Office XP Introductory Concepts and Techniques
- Financial Peace: Revisited
- How to Use Financial Statements: A Guide to Understanding the Numbers
- Keys to Reading an Annual Report (Barron's Business Keys)
- Flipping Properties: Generate Instant Cash Profits in Real Estate
- Business Analysis with Microsoft Excel (2nd Edition)
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