Share with your network!

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.

SUMPRODUCT

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)

formula for SUMPRODUCT function in excel

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.

how to use SUMPRODUCT function in excel

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.

Using SUMPRODUCT function with a criteria

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.

formula for SUMPRODUCT using a criteria

3. If your formula is correctly mentioned then after pressing enter you shall receive your desired answer.

how to use SUMPRODUCT function with a criteria

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.

Multiple criteria in SUMPRODUCT function

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))

Multiple criteria in SUMPRODUCT function formula

3. If you were careful while adding your brackets and asterisks, you shall receive your desired result.

how to use Multiple criteria in SUMPRODUCT function formula

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.