While working with Excel, there are situations when you are comparing data between two or more ranges and calculating with multiple criteria. The SUMPRODUCT function is an extremely resourceful function for these purposes. It has the unique ability to handle arrays in smart and elegant ways. Often, we need to use this function to compare between columns with given criteria and to find the result. Today in this article, we will discuss some methods of using the SUMPRODUCT function with criteria.

**Excel SUMPRODUCT Function: Overview**

Technically, the **“SUMPRODUCT”** function remits the summation of the values of corresponding arrays or ranges.

**⇒ Syntax**

The syntax of the** “SUMPRODUCT”** function is simple and direct.

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

**⇒ ****Argument**

Argument | Required/Optional | Explanation |
---|---|---|

array1 |
Required |
The first input to an array, whose elements you want to divide and afterward add. |

[array2], [array3] |
Optional |
Array parameters with elements you want to multiply and add, ranging from 2 to 255. |

* Note:* One of the amazing features of the

**SUMPRODUCT**function is it can handle single or multiple criteria remarkably well. Let’s discuss some of the

**SUMPRODUCT**with criteria functions.

**1. Applying ****SUMPRODUCT with a Single Criterion to Lookup Value**

We can apply the **SUMPRODUCT **function with criteria with or without the double unary operator.

**1.1. Using Double Unary Operator**

A smart way to apply **the SUMPRODUCT function** is to insert the criteria within the function as an array using “**Double unary operator (–)**” to convert the** “TRUE”** or **“FALSE”** into **“1”** or **“0”**. In the following example, some **“Product”** names are given with their **“Country”**, **“Qty”**, and **“Price”**. We will find the total price for countries** “India”,** **“China”**, and **“Germany”**.

Let’s follow the procedures to use the **SUMPRODUCT** function with single criteria in Excel.

**STEPS:**

- Firstly, create a table for these countries anywhere in the worksheet where you want to get the result.
- Secondly, select the cell where you want to put the formula of the
**SUMPRODUCT**function. - Thirdly, insert the formula into that cell. We apply the function with the
**“Double Unary Operator (–)”**.

`=SUMPRODUCT(--($C$5:$C$21=G5),$D$5:$D$21,$E$5:$E$21)`

- Then, press the
**Enter**key. We have got the total price for**“India”**.

- Drag the
**Fill Handle**icon down to duplicate the formula over the range. Or, to**AutoFill**the range,**double-click**on the plus (**+**) symbol.

- Finally, we can see the results for
**India**,**China**, and**Germany**.

**🔎**** How Does the Formula Work?**

is**Array1****–($C$5:$C$21=G5)****“India”**. The double unary operator will convert the results frominto*$C$4:$C$20***“1”**and**“0”**.is**[Array2]**, which range we first multiply and then add.**$D$5:$D$21**is**[Array3]**, also this range we multiply and then add.**$E$5:$E$21**

We will use the **“Absolute Cell References”** to **“BLOCK”** the cells.

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

**1.2. Excluding Double Unary Operator**

We can solve the previous example without using the double unary operator. Let’s see the instructions for this.

**STEPS:**

- We will use the same example to get the same result. Now, in Cell
**“H5”**apply the**SUMPRODUCT**function. Insert the values into the formula and the formula is like this.

`=SUMPRODUCT(($C$5:$C$21=G5)*$D$5:$D$21*$E$5:$E$21)`

- Further, hit the
**Enter**key to show the outcome.

- To copy the formula over the range, drag the
**Fill Handle**symbol downward. Alternatively, you can**double-click**the addition (**+**) sign to**AutoFill**the range. - Similarly, we can get the result.

**2. Using ****SUMPRODUCT with Multiple Criteria for Different Columns**

We can apply the** SUMPRODUCT **function with multiple criteria with or without the double unary operator. Let’s learn!

### 2.1. Using Double Unary Operator

In this case, we will use the **“Double Unary Operator (–)”** to convert the array results into** “1” **or** “0”**. Consider the same table that we used before. Now we will find the result using multiple criteria. We will find the **“Total Price”** for **“processor”** of the country** “China”**, **“Notebook”** of **“India”** and** “Laptop”** of **“South Korea”**. Using these criteria we will derive the result.

**STEPS:**

- In the beginning, select a cell adjacent to the first book and enter the formula.

`=SUMPRODUCT(--($B$5:$B$21=G5),--($C$5:$C$21=H5),$D$5:$D$21,$E$5:$E$21)`

- Press the
**Enter**key on your keyboard once more.

- Likewise, in the previous examples, drag the
**Fill Handle**icon down to duplicate the formula over the range. Or, to**AutoFill**the range,**double-click**on the plus (**+**) symbol. - Finally, you will get your results.

### 2.2. Excluding Double Unary Operator

Here we will apply the same multiple criteria using the basic** SUMPRODUCT** function.

**STEPS:**

- In cell
**I5,**apply the function. Insert the criteria and the formula looks like this.

`=SUMPRODUCT(($B$5:$B$21=G5)*($C$5:$C$21=H5)*$D$5:$D$21*$E$5:$E$21)`

- Hit
**Enter**to see the result.

- After that, drag the
**Fill Handle**icon to copy the formula over the range. Or,**double-click**on the plus (**+**) sign. This also duplicates the formula. - Finally, you can see the outcome.

**Read More:** How to Use SUMPRODUCT to Lookup Multiple Criteria in Excel

**3. Inserting ****SUMPRODUCT Function with OR Logic**

We can add **OR** logic to our formula to make the **SUMPRODUCT** function with criteria more dynamic. Consider the situation where we need to find out the total price for** “Notebook”** and** “Laptop”**.

**STEPS:**

- Firstly, create a table anywhere in the worksheet where you want to get the result.
- Then, select the cell and insert the following formula there.

`=SUMPRODUCT(--((B5:B21=G5)+(B5:B21=H5)>0),D5:D21,E5:E21)`

- Further, hit the
**Enter**key to see the outcome.

**Related Content: **Excel SUMPRODUCT Function Based on Date Range

**4. Applying ****SUMPRODUCT with Multiple AND/OR Criteria**

In this method, we will apply the SUMPRODUCT function with criteria using both **“AND”**, **“OR”** logic. This time we have to add more criteria to our function. We will retrieve the **“Total Price”** for the product **“Notebook”**,** “Laptop”** of the country **“India”, “China”.**

**STEPS:**

- To begin with, select the second cell
**H10**, and put the formula into that selected cell.

`=SUMPRODUCT(--((B5:B21=G5)+(B5:B21=H5)>0),--((C5:C21=G8)+(C5:C21=H8)>0),D5:D21,E5:E21)`

- Then, press the
**Enter**key to see the outcome.

**🔎**** How Does the Formula Work?**

is**1**Here*–((B5:B21=G5)+(B5:B21=H5)>0),–((C5:C21=G8)+(C5:C21=H8)>0)*.**B5:B21**is the**“Product”**Column,**G5**and**H5**are**“Notebook”**and**“Laptop”**. Similarly,is the**C5:C21****“Country”**column, and**G6**and**H6**are**“India”**and**“China”.**is**[Array2]****D5:D21.**is**[Array3]***E5:E21*.

**Read More: **How to Use SUMPRODUCT IF in Excel

**5. Using ****SUMPRODUCT with Multiple Criteria for Rows and Columns **

The** “SUMPRODUCT”** function shows its true diversity when we use this function for both columns and rows. Let’s see how. In the following example, we can see the price of some** “Products”** from the country **“India”**, **“China”**, **“Italy”**, **“Germany”**, **“France”**.

**STEPS:**

- Firstly, select the cell where we want to put the result.
- Then, insert the formula into that cell.

`=SUMPRODUCT(C5:G8*(C4:G4=C10)*(B5:B8=C11))`

- Finally, press
**Enter**key from the keyboard.

**Read More: **Excel SUMPRODUCT with Multiple Criteria in Same Column

**Things to Remember**

✅ The** “SUMPRODUCT”** function treats non-numeric values as zeros. If you have any non-numeric values in your formula the answer will be **“0”.**

✅ Arrays in the SUMPRODUCT formula must have the same number of rows and columns. If not, you get the** #VALUE! **Error.

✅ The **“SUMPRODUCT”** function does not support wildcard characters.

**Download Practice Workbook**

Download this practice sheet to practice the task while you are reading this article.

**Conclusion **

The **SUMPRODUCT** function is one of the most diversified functions in Excel. In this article, we covered **the SUMPRODUCT function** with single or multiple criteria. We hope that this article proves useful to you. If you have any confusion or suggestions, you are welcome to comment.

**Related Articles**

- SUMPRODUCT Across Multiple Sheets in Excel
- SUMPRODUCT for Counting with Multiple Criteria in Excel
- [Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel

**<< Go Back to Excel SUMPRODUCT Function | Excel Functions | Learn Excel**

I believe there’s an error in “5) SUMPRODUCT with Multiple Criteria for Rows and Columns “: the answer calcualated by SUMPRODUCT()($63,800) should actually be only $56,000 (the intersection of Laptop and China).

Dear FLEET,

Thank you for your response.

The answer for the intersection of “Laptop” and “China” will be “$63,800”.

The SUMPRODUCT function returns the sum of an array from an argument. With the intersection between “China” and Laptop” within the cell range we got two outputs which are “$56,000” and “$7,800”. Thus summing the total value with the SUMPRODUCT function stands to “$63,800”.

You can check the screenshot below.

We have also attached a worksheet with our article. You can practice the formula there, too. Thanks!

Thanks for referring, I will check.

u made my day. i was struggling with multiple selections!… thanks a lot

Hello, KRISHNAN V! Thanks for your feedback. Hope you’ll find our other articles useful as well when needed for your works!

How can I use the SUMPRODUCT for adding sums from different columns that are under the same category in a drop-down list? For this example, I split up my expenses for each pay check and then I categorize each expense by an “expense type”. I want each expense for each expense type to be added together to a separate table that I will then use those sums to create a pie chart. For example, I have a pay check on 9/15 where my Lifestyle expense = $200 and a pay check on 9/30 where the Lifestyle expense= $350. In the other table, I want to be able to add the sum of $200+$350. Can SUMPRODUCT help me with this? I have been struggling a lot to figure this out! I can provide a screen shot of the excel chart if needed.

Hello FELICIA SANTOS,

I hope you are doing well! Here, I have created a dataset as you have described and calculated the total amount spent per category.

>> Here, you have to insert the name of the

categoryin cellF5.>> Then, insert the following formula into cell

G5:`=SUMPRODUCT(D3:D10*(C3:C10=F5))`

Thus, you will get the sum of the amount as per the selected category.

I hope, your problem will be solved in this way. You can share more problems in an email at

[email protected]Hi,

What if I want to sum Notebook from India up to France (Result = $ 167,000.00)

or

Product : Notebook

From : China

To : Germany

Result = $114,000

From & To in dropdown list

Hello DRIN,

Thanks for your comment.

If you want to sum Notebook from India up to France and use drop-down for the countries, then use the following formula in cell C13:

=SUM(INDEX($B$5:$G$8,MATCH(C10,$B$5:$B$8,0),MATCH(C11,$C$4:$G$4,0)+1):INDEX($B$5:$G$8,MATCH(C10,$B$5:$B$8,0),MATCH(C12,$C$4:$G$4,0)+1))

It’ll give the desired result.

Change the countries to China and Germany from the drop-down and the total value will be updated.

If you have other queries let me know in the comment.

Regards,

Sajid Ahmed

Exceldemy

very help information. I managed to resolve my equation for sumproduct.

Thanks a lot

KS

Hello,

You are most welcome.

Regards

ExcelDemy