How to Use SUMPRODUCT Function in Excel (4 Examples)

Quick View of the SUMPRODUCT Function

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)

Quick View of the SUMPRODUCT Function

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

Syntax of the SUMPRODUCT Function

The Syntax of the SUMPRODUCT function is:

=SUMPRODUCT(array1,[array2],[array3],...)

Argument

Argument Required or Optional Value
array1 Required The first array of numbers.
[array2] Optional The second array of numbers.
[array3] Optional The third array of numbers.

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.

A Data Set in Excel

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:

=SUMPRODUCT(--(C5:C19>=80))

SUMPRODUCT for Single Criteria

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.
Formula Output Explanation
=SUMPRODUCT(–(C5:C19>=80)) 8 First returns an array of 1 and 0. 1 for a number greater than or equal to 80, 0 otherwise. Then returns the sum of all the 0’s and 1’s of the array, 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:

=SUMPRODUCT(--(C5:C19>=80),--(D5:D19>=80))

SUMPRODUCT for Multiple Criteria

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.

Formula Output Explanation
=SUMPRODUCT(–(C5:C19>=80),–(D5:D19>=80)) 5 –(C5:C19>=80) returns an array of 1 and 0. 1 when the mark in Physics is at least 80, 0 otherwise.

–(D5:D19>=80) also returns an array of 1 and 0. 1 when the mark in Chemistry is at least 80, 0 otherwise.

Then it multiplies the two arrays, after that returns the sum of the products.

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:

=SUMPRODUCT(--(((C5:C19>=80)+(D5:D19>=80))>0))

SUMPRODUCT for Multiple OR Criteria

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.
Formula Output Explanation
=SUMPRODUCT(–(((C5:C19>=80)+(D5:D19>=80))>0)) 10 Returns the number of students with at least 80 in at least any 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:

=SUMPRODUCT(C5:C19,--(C5:C19>=80))

SUMPRODUCT for Total Values with Criteria

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.

Formula Output Explanation
=SUMPRODUCT(C5:C19,–(C5:C19>=80)) 677 Returns the sum of only those numbers in Physics that are greater than or equal to 80.

Common Errors with Excel SUMPRODUCT Function

Error When They show
#VALUE! This shows when the lengths of all the arrays are not the same. It also shows when an argument is of the wrong data type, that means, if an argument is not an array.

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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo