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)

**Table of Contents**hide

**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],...)`

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

converts the array of`--(C5:C19>=80)`

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

returns the sum of the array`SUMPRODUCT(--(C5:C19>=80))`

`--(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**

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

So it returnsÂ `{0, 1, 1, 0, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 1}`

.

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

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**

returns an array of`C5:C19>=80`

**TRUE**or**FALSE**.**TRUE**for a mark in Physics greater than or equal to 80,**FALSE**otherwise.- Similarly,
returns another array of`D5:D19>=80`

**TRUE**or**FALSE**.**TRUE**for a mark in Chemistry greater than or equal to 80,**FALSE**otherwise. adds these two boolean arrays. But while adding, it counts each`((C5:C19>=80)+(D5:D19>=80))`

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

turns it into an array of`(((C5:C19>=80)+(D5:D19>=80))>0)`

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

**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**

returns an array of`--(C5:C19>=80`

**1â€™**s and**0**â€™s.**1**when the marks in Physics is greater than or equal to 80,**0**otherwise.first multiplies the array`SUMPRODUCT(C5:C19,--(C5:C19>=80))`

with the array`C5:C19`

`--(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.