Rifat Hassan
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:
Download Practice Workbook
You can download our practice workbook from here for free!
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:
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}.
7 Suitable Examples of Using SUMPRODUCT Function in Excel
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.
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.
Thus, you will see, the number of students who have achieved at least 80 in Physics, 8.
Read More: 51 Mostly Used Math and Trig Functions in Excel
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))
🔎 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.
Read More: 44 Mathematical Functions in Excel (Download Free PDF)
Similar Readings
- How to Use SUMIF Function in Excel (With 5 Easy Examples)
- Use SUMIFS Function in Excel (6 Handy Examples)
- How to Use MOD Function in Excel (9 Suitable Examples)
- Use ROUND Function in Excel (With 9 Examples)
- How to Use RANDBETWEEN Function in Excel (4 Examples)
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.
Consequently, you will get a total of 10 who got at least 80 in one subject or both.
Read More: How to Use PRODUCT Function in Excel (With 9 Examples)
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 which 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.
As a result, you will get a total of marks greater than or equal to 80 in Physics is 677.
Read More: How to Use SUM Function in Excel (With 6 Easy Examples)
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.
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.
Thus, you will get your desired result in cell H5 and that is 6925.
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.
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.
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.
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)
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.
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.
Visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!
