How to Use SUMPRODUCT Function in Excel (4 Examples)

Today I will be showing how you can multiply two or more arrays and then determine its sum using the SUMPRODUCT function of Excel.

SUMPRODUCT Function of Excel (Quick View)

Download Practice Workbook

Excel SUMPRODUCT Function: Syntax and Argument

Summary

• SUMPRODUCT takes one or more arrays as an argument, multiplies the corresponding values of all the arrays, and then returns the sum of the products.
• Though it works with arrays, it is a Non-Array Formula. You need not press Ctrl + Shift + Enter to insert this function.
•  Available from Excel 2003.

Syntax

The Syntax of the SUMPRODUCT function is:

Argument

Note:

• The lengths of all the arrays must be equal. Otherwise, Excel will raise a #VALUE! Error.
• Normally the arrays within the SUMPRODUCT function contain all numbers. But if any cell within an array contains a text value other than a number, SUMPRODUCT will count it as 0.
• If an array contains all Boolean values (TRUE and FALSE), you can insert a “–“ before it to convert it to an array of numbers. 1 for a TRUE, 0 for a FALSE.

For example, --{TRUE,FALSE,TRUE,TRUE,FALSE} = {1,0,1,1,0}.

Return Value

Returns the sum of the products of the corresponding values from all the arrays.

Excel SUMPRODUCT Function: 4 Examples

1. Counting Cells with Specific Criteria

You can use the SUMPRODUCT function to count the number of cells with a specific criteria.

Look at the data set below. We have the marks in Physics and Chemistry of some students of a school named Saint Xaviers School.

Now we will try to find out how many students got at least 80 in Physics with the help of the SUMPRODUCT function.

The formula will be:

See, we have got the number of students who have achieved at least 80 in Physics, 8.

Explanation of the Formula

• C5:C19>=80 goes through each cell of the array C5:C19 and checks whether it is greater than or equal to 80 or not.

If greater than or equal to 80, it returns a TRUE, otherwise FALSE.

So it returns {FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE}.

• --(C5:C19>=80) converts the array of TRUE and FALSE into an array of 1 and 0. 1 for a TRUE, 0 for a FALSE.

So it returns {0, 1, 1, 0, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 1}.

• SUMPRODUCT(--(C5:C19>=80)) returns the sum of the array --(C5:C19>=80) = {0, 1, 1, 0, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 1}.

So it returns 0+1+1+0+1+0+0+1+1+1+1+0+0+0+1=8.

• So, the total number of students with at least 80 in Physics is 8.

2. Counting Cells with Multiple Criteria (AND Type)

Now let us try to count the total number of students with at least 80 in both Physics and Chemistry,

With the help of the SUMPRODUCT function, obviously.

The formula to find out that will be:

See, we have got the number of students with at least 80 in both the subjects. It is 5.

Explanation of the Formula

• --(C5:C19>=80) returns an array of 1’s and 0’s. 1 for a number greater than or equal to 80 in Physics, 0 otherwise.

So it returns  {0, 1, 1, 0, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 1}.

• And --(D5:D19>=80) returns an array of 1’s and 0’s. 1 for a number greater than or equal to 80 in Chemistry, 0 otherwise.

So it returns  {1, 1, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 1}.

• SUMPRODUCT(–(C5:C19>=80),–(D5:D19>=80)) first multiplies the two arrays.

As we know, something multiplied by a 0 is also a 0, therefore it will return 1 only when the marks in both the subjects are greater than or equal to 80.

Then it returns the sum of the products.

{0, 1, 1, 0, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 1} * {1, 1, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 1} = {0, 1, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 0, 1}.

And the sum of the products= 0+1+0+0+1+0+0+0+0+1+1+0+1=5.

This is the number of students with at least 80 in both the subjects.

3. Counting Cells with Multiple Criteria (OR Type) 

This time, let us try to count the total number of students who got at least 80 in at least one subject, either in Physics or in Chemistry or both.

The SUMPRODUCT formula to count this will be:

See, there are a total of 10 students who got at least 80 in one subject or both.

Explanation of the Formula

• C5:C19>=80 returns an array of TRUE or FALSE. TRUE for a mark in Physics greater than or equal to 80, FALSE otherwise.
• Similarly, D5:D19>=80 returns another array of TRUE or FALSE. TRUE for a mark in Chemistry greater than or equal to 80, FALSE otherwise.
• ((C5:C19>=80)+(D5:D19>=80)) adds these two boolean arrays. But while adding, it counts each TRUE as 1 and each FALSE as 0.

Therefore it returns a 2 when marks in both subjects are at least 80.

Returns a 1 when in one subject, the mark is at least 80.

And returns a 0 when marks in both subjects are less than 80.

It returns {1, 2, 1, 0, 2, 0, 0, 1, 1, 2, 2, 0, 1, 0, 2}.

• (((C5:C19>=80)+(D5:D19>=80))>0) turns it into an array of TRUE and FALSE. TRUE when the value is greater than 0, which means 1 and 2, and FALSE when the value is 0.
• --(((C5:C19>=80)+(D5:D19>=80))>0) again turns it into an array of 1 and 0. 0 in place of a FALSE, and 1 in place of a TRUE.

It returns {1, 1, 1, 0, 1, 0, 0, 1, 1, 1, 1, 0, 1, 0, 1} 

• SUMPRODUCT then returns the sum of this array, 1+1+1+0+1+0+0+1+1+1+1+0+1+0+1 = 10
• Thus we get the number of students who got at least 80 in at least one subject.

4. Calculating Total Values Maintaining a Criteria

This is our final project today. We will calculate the total marks of the students in Physics, but only those marks which are greater than or equal to 80.

Obviously with the help of the SUMPRODUCT function.

The formula will be:

See, the total of the marks greater than or equal to 80 in Physics is 677.

Explanation of the Formula

• --(C5:C19>=80 returns an array of 1’s and 0’s. 1 when the marks in Physics is greater than or equal to 80, 0 otherwise.
• SUMPRODUCT(C5:C19,--(C5:C19>=80)) first multiplies the array C5:C19 with the array --(C5:C19>=80). 

We know, something multiplied by a 0 is a 0. Therefore it returns only the numbers that are greater than or equal to 80, and returns 0 for the rest.

It returns {0, 82, 81, 0, 91, 0, 0, 87, 93, 81, 80, 0, 0, 0, 82}.

Then it returns the sum of the array, 0+82+81+0+91+0+0+87+93+81+80+0+0+0+82 = 677.

Common Errors with Excel SUMPRODUCT Function

Conclusion

Using these methods, you can use the SUMPRODUCT function of Excel to perform various required tasks in Excel. Do you have any questions? Feel free to inform us.