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:
=SUMPRODUCT(array1,[array2],[array3],...)
Read More: SUMPRODUCT() function in Excel
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.
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))
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))
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))
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))
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 arrayC5: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.
Further Readings
- How to Use SUM Function in Excel (With 6 Easy Examples)
- How to Use SUMIF Function in Excel (With 5 Easy Examples)
- How to Use SUMIFS Function in Excel (6 Handy Examples)
- How to Use MOD Function in Excel (9 Suitable Examples)
- How to Use ROUND Function in Excel (With 9 Examples)
- How to Use RANDBETWEEN Function in Excel (4 Examples)
- How to Use SEQUENCE Function in Excel (16 Examples)
- How to use RAND function in Excel (5 Examples)
- How to Use INT Function in Excel (With 8 Examples)
- Solving equations in Excel (polynomial, cubic, quadratic, & linear)