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

**Table of Contents**Expand

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

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

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

### 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: **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.

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.

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
**Ente**r key.

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.

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.

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

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.

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