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.
Syntax of SUMPRODUCT
SUMPRODUCT(array1,array2,array3, …) wherein
Array1,array2,array3 = the components which you want to multiply and add. (You can add up to 255 arrays)
How to use SUMPRODUCT Function in Excel?
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.
Applying Criteria to SUMPRODUCT Function
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.
Applying Multiple Criteria to SUMPRODUCT Function
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.
Related links you will like:
Comments