|  

» Performing calculations based on multiple criteria, using SUMPRODUCT function

Problem:

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%
Screenshot // Performing calculations based on multiple criteria, using SUMPRODUCT function
Performing calculations based on multiple criteria, using SUMPRODUCT function


Rate This Tip
12 34 5
Rating: 3.24     Views: 27043
Try using the Conditional Sum Wizard
Padgett
Tools...Addins
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
Click here to post comment
For Registered Users
Name
Comment Title
Comments