How to Use Excel SUMPRODUCT Function (7 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Excel has several fantastic functions for large and extensive calculations. The SUMPRODUCT function is one of the most fantastic functions in Excel. It can work with multiple arrays and then return the sum of the values maintaining the criteria. In this article, I will show you how to use the Excel SUMPRODUCT function with 7 suitable examples.

A Quick Overview of Excel SUMPRODUCT Function:

Excel SUMPRODUCT Function Overview


Introduction to 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:

SUMPRODUCT function syntax

The Syntax of the SUMPRODUCT function is:

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

Argument:

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

Return Value:

It returns the sum of the products of the corresponding values from all the arrays.

Available in:

Excel 2007 to 2021 and Excel 365.

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}.

How to Use SUMPRODUCT Function in Excel: 7 Suitable Examples

Say, we have a dataset of student records of Saint Xavier’s school containing 3 columns named Student Name, Marks in Physics, and Marks in Chemistry.

Sample Dataset to Use Excel SUMPRODUCT Function

Now, we need to find the count or total numbers according to different criteria using the SUMPRODUCT function. Follow the article below to learn about this.


1. Counting Cells with Specific Criteria Using SUMPRODUCT Function

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

Say, you want to calculate how many students have got 80 or above in Physics. Now, follow the steps below to achieve this result.

📌 Steps:

  • First and foremost, click on cell F6.
  • Following, insert the formula below.
=SUMPRODUCT(--(C6:C17>=80))

🔎 Formula Breakdown:

  • C6:C17>=80 

It goes through each cell of the array C6:C17 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.

Result: {FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE}.

  • –(C6:C17>=80) 

It converts the array of TRUE and FALSE into an array of 1 and 0. 1 for a TRUE, 0 for a FALSE.

Result: {0, 1, 1, 0, 1, 0, 1, 1, 1, 1, 0, 1}.

  • SUMPRODUCT(–(C6:C17>=80)) 

It returns the sum of the array –(C6:C17>=80) = {0, 1, 1, 0, 1, 0, 1, 1, 1, 1, 0, 1}.

Result: 0+1+1+0+1+0+1+1+1+1+0+1=8.

  • Subsequently, hit the Enter key.

Excel SUMPRODUCT Function to Count Cells with Specific Criteria

Thus, you will see, the number of students who have achieved at least 80 in Physics, 8.


2. Counting Cells with Multiple Criteria with SUMPRODUCT Function (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. Follow the steps below to do this.

📌 Steps:

  • First, click on cell F6.
  • Following, insert the formula below and press the Enter key.
=SUMPRODUCT(--(C6:C17>=80),--(D6:D17>=80))

Use SUMPRODUCT Function to Count Cells with Multiple Criteria (AND Type)

🔎 Formula Breakdown:

  • –(C6:C17>=80)

It returns an array of 1’s and 0’s. 1 for a number greater than or equal to 80 in Physics, 0 otherwise.

Result:  {0, 1, 1, 0, 1, 0, 1, 1, 1, 1, 0, 1}.

  • –(D6:D17>=80)

It returns an array of 1’s and 0’s. 1 for a number greater than or equal to 80 in Chemistry, 0 otherwise.

Result: {1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 1}.

  • SUMPRODUCT(–(C6:C17>=80),–(D6:D17>=80))

First, It 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 subjects are greater than or equal to 80. Then it returns the sum of the products.

Result: {0, 1, 1, 0, 1, 0, 1, 1, 1, 1, 0, 1} * {1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 1} = {0, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 1} = 0+1+0+0+1+0+0+0+1+1+0+1=5.

So, you will get the number of students with at least 80 in both subjects and It is 5.


3. Use SUMPRODUCT for 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 Chemistry or both. Go through the steps below to do this.

📌 Steps:

  • At the very beginning, click on cell F6.
  • Following, insert the formula below.
=SUMPRODUCT(--(((C6:C17>=80)+(D6:D17>=80))>0))

🔎 Formula Breakdown:

  • C6:C17>=80

It returns an array of TRUE or FALSE. TRUE for a mark in Physics greater than or equal to 80, FALSE otherwise.

Result: {FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE}.

  • D6:D17>=80

It returns another array of TRUE or FALSE. TRUE for a mark in Chemistry greater than or equal to 80, FALSE otherwise.

Result: {TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE}

  • ((C6:C17>=80)+(D6:D17>=80))

It 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.

Result: {1,2,1,0,2,0,1,1,2,2,0,2}.

  • (((C6:C17>=80)+(D6:D17>=80))>0)

It 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.

Result: {TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE}

  • –(((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.

Result: {1,1,1,0,1,0,1,1,1,1,0,1}

  • SUMPRODUCT(–(((C6:C17>=80)+(D6:D17>=80))>0))

It then returns the sum of this array.

Result: 1+1+1+0+1+0+0+1+1+1+1+0+1+0+1 = 10

  • Finally, press the Enter key.

Use SUMPRODUCT Function to Count Cells with Multiple OR Criteria

Consequently, you will get a total of 10 who got at least 80 in one subject or both.

Read More: SUMPRODUCT for Counting with Multiple Criteria in Excel


4. Calculating Total Values Maintaining a Criteria with SUMPRODUCT

Now, say you want to calculate the total marks of the students in Physics, but only those marks that are greater than or equal to 80 with the help of the SUMPRODUCT function.

📌 Steps:

  • Initially, click on cell F6.
  • Afterward, insert the following formula.
=SUMPRODUCT(C6:C17,--(C6:C17>=80))

🔎 Formula Breakdown:

  • –(C6:C17>=80)

It returns an array of 1’s and 0’s. 1 when the marks in Physics is greater than or equal to 80, 0 otherwise.

Result: {0, 1, 1, 0, 1, 0, 1, 1, 1, 1, 0, 0, 0, 1}.

  • SUMPRODUCT(C6:C17,–(C6:C17>=80))

It first multiplies the array C6:C17 with the array –(C6:C17>=80). Then sums up those numbers.

Result: 0+82+81+0+91+0+87+93+81+80+0+82 = 677.

  • Subsequently, press the Enter key.

Use SUMRPODUCT Function to Sum Total Marks with Specific Criteria

As a result, you will get a total of marks greater than or equal to 80 in Physics is 677.


5. Use SUMPRODUCT-IF Formula to Apply Specific Criteria in Excel

Now, say you have a dataset of sales of different items in different regions with their quantity and unit prices.

Sample Dataset to Apply SUMPRODUCT IF Formula

Now, say you need to find a special item’s total price only. You can do this by incorporating the IF function inside the SUMPRODUCT function. Go through the steps below to achieve this.

📌 Steps:

  • First, insert the desired item’s name in cell G5.
  • Afterward, click on cell H5 and insert the following formula.
=SUMPRODUCT(IF($C$5:$C$17=$G$5,$D$5:$D$17*$E$5:$E$17))
  • Subsequently, hit the Enter key.

SUMPRODUCT IF Formula

Thus, you will get your desired result in cell H5 and that is 6925.

Read More: How to Use SUMPRODUCT with Criteria in Excel


6. Use Excel SUMPRODUCT with Multiple Criteria in Rows and Columns

Now, say, you have a dataset of products with customer names and prices each month of January, February, and March.

Dataset to Apply SUMPRODUCT in Multiple Rows and Columns

You can use the advanced Excel SUMPRODUCT function with multiple criteria in rows and columns. Follow the steps below to do this.

📌 Steps:

  • Initially, insert your desired criteria in cells H5 and I5.
  • Afterward, click on cell J5 and insert the following formula.
=SUMPRODUCT((C5:C13=H5)*(D4:F4=I5),D5:F13)
  • Subsequently, hit the Enter key.

SUMPRODUCT with Multiple Criteria in Rows and Columns

Read More: How to use SUMPRODUCT Function with Multiple Columns in Excel


7. Calculate Weighted Average with Excel SUMPRODUCT Function

Now, say you have a dataset of student’s grading systems. You are given the weight of assessments and marks got by an individual.

Marks Assessment System

Now, you need to calculate the weighted average of the student. You can achieve this by using SUMPRODUCT and SUM functions. Go through the steps below to do this.

📌 Steps:

  • First, click on merged cell E5.
  • Following, insert the formula below and hit the Enter key.
=SUMPRODUCT(C5:C9*D5:D9)/SUM(C5:C9)

Calculate Weighted Average with SUMPRODUCT Function

Thus, you will get a weighted average of 77.9 in cell E5.


💬 Things to Remember

  •  It shows an #VALUE! error, when an argument is of the wrong data type, that means, if an argument is not an array.

Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

So, in this article, I have shown you 4 suitable examples of the Excel SUMPRODUCT function. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. And, you are very welcome to comment here if you have any further questions or recommendations regarding this article.


Excel SUMPRODUCT Function: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo