The SUMPRODUCT Function in Excel, multiplies the corresponding components in the given arrays, and returns the sum of the product. This function eases the calculation part in the Excel sheet.
SUMPRODUCT(array1,array2,array3, ...) wherein
Array1,array2,array3 = the components which you want to multiply and add. (You can add up to 255 arrays)
1. In the example below, we want to know the total commission to be paid by us. One way of doing this is, you can calculate the commission of each employee differently in column E( Sales*Commission) and then add it together. But that will be too long so we use SUMPRODUCT function instead.
2. Mention your arrays in the formula separating it with the commas. Even if you use asterisk instead of comma in this case, you shall receive the same result. So in our case formula will be =SUMPRODUCT(C2:C8,D2:D8) or =SUMPRODUCT(C2:C8*D2:D8)
3. Press enter and you shall achieve your desired results. See the images below and you will realize that it doesn’t matter whether you use an asterisk or comma to separate the array. But when you are using any conditions or criteria make sure you use asterisk instead of comma.
1. There may be times when you want to apply a condition to the SUMPRODUCT formula. Say for instance, I want to know the total commission of the employees in NewYork in the below mentioned excel sheet.
2. In this case we shall use the asterisk (*) instead of comma, since we are adding a condition to it. Your formula here should be =SUMPRODUCT((C2:C8*D2:D8)*(B2:B8="NewYork")). Make the proper use of asterisks and brackets.
3. If your formula is correctly mentioned then after pressing enter you shall receive your desired answer.
1. Using the SUMPRODUCT function, you can apply multiple conditions as well. In the excel sheet below we shall now find the SUMPRODUCT of total commission of the employees working in NewYork during night shift.
2. Here the formula will contain three arrays that is, your original array and the two other conditions. So the formula will be =SUMPRODUCT((D2:D8*E2:E8)*(C2:C8="NewYork")*(B2:B8="Day shift))
3. If you were careful while adding your brackets and asterisks, you shall receive your desired result.
If you have any questions then do mention them in our comments box below and we shall get back to you as early as possible.
Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc
CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.
Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to firstname.lastname@example.org and we will rectify it.
2015 © Edupristine. ALL Rights Reserved.