» 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:
- Now, Discover Your Strengths
- What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade
- Flipping Properties: Generate Instant Cash Profits in Real Estate
- The Guide to Understanding Financial Statements
- Treason: Liberal Treachery from the Cold War to the War on Terrorism
- Marketing Planning for Services
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