# SUMPRODUCT with Criteria in Excel (5 Methods) There are situations when you are comparing data between two or more ranges and calculating with multiple criteria, The SUMPRODUCT function is your first choice. The SUMPRODUCT function is an extremely resourceful function with many purposes. It has the unique ability to handle arrays in smart and elegant ways. Often we need to use the SUMPRODUCT 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

Technically, the “SUMPRODUCT” function returns the sum of the values of corresponding arrays or ranges. Normally, the default operation is multiplication, but addition, subtraction, and division can also be done.

### Syntax

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

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

### Argument

• [array1]: The first array or ranges of cells whose components you want to multiply and then add (required).
• [ array2], [array3]…: Array arguments 2 to 255 whose values you want to multiply and then add (Optional)

## SUMPRODUCT with Criteria (5 Methods)

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) SUMPRODUCT with a Single Criterion

We can apply the “SUMPRODUCT” with criteria with or without the double unary operator.

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

Step-1:

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”. Step-2:

Now create a table for these countries anywhere in the worksheet where you want to get the result. Step-3:

Apply the function with the “Double Unary Operator (–)”.

The final formula is,

=SUMPRODUCT(–(\$C\$4:\$C\$20=G4),\$D\$4:\$D\$20,\$E\$4:\$E\$20)

Where,

• Array1 is –(\$C\$4:\$C\$20=G4). G4 is “India”. The double unary operator will convert the results from “\$C\$4:\$C\$20” into “1” and “0”
• [Array2] is \$D\$4:\$D\$20.
• [Array3] is \$E\$4:\$E\$20.

We will use the “Absolute Cell References” to “BLOCK” the cells. Press “Enter”. We have got the total price for “India”. Step-4:

Now select the cell “H4” and drag to get the final result. #### ii. Without Using Double Unary Operator

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

Step-1:

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

=SUMPRODUCT((\$C\$4:\$C\$20=G4)*\$D\$4:\$D\$20*\$E\$4:\$E\$20)

Where,

• Array1 is (\$C\$4:\$C\$20=G4). G4 is “India”.
• [Array2] is \$D\$4:\$D\$20.
• [Array3] is \$E\$4:\$E\$20. Press “Enter”. Our result is here. Step-2:

Now apply the same for the rest of the cells. ### 2) 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!

#### i. Using Double Unary Operator

In this case, we will use the “Double Unary Operator (–)” to convert the array results into “1” or “0”

Step-1:

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. Step-2:

Apply the “SUMPRODUCT” function with multiple criteria.

The final formula is

=SUMPRODUCT(–(\$B\$4:\$B\$20=G4),–(\$C\$4:\$C\$20=H4),\$D\$4:\$D\$20,\$E\$4:\$E\$20)

Where,

• Array1 is –(\$C\$4:\$C\$20=G4),–(\$C\$4:\$C\$20=H4). “G4”, “H4” is “Processor”, “China”. The double unary operator will convert the results from \$C\$4:\$C\$20 and \$C\$4:\$C\$20 into “1” and “0”.
• [Array2] is \$D\$4:\$D\$20.
• [Array3] is \$E\$4:\$E\$20. Press “Enter” to apply these criteria. Step-3:

We got our result. Now apply the same criteria for the other two cells. #### ii. Without Using Double Unary Operator

Here we will apply the same multiple criteria using the basic “SUMPRODUCT” function

Step-1:

In cell “I4” apply the function. Insert the criteria and the formula looks like

=SUMPRODUCT((\$B\$4:\$B\$20=G4)*(\$C\$4:\$C\$20=H4)*\$D\$4:\$D\$20*\$E\$4:\$E\$20)

Where,

• Array1 is (\$C\$4:\$C\$20=G4), (\$C\$4:\$C\$20=H4). “G4”, “H4” is “Processor”, “China”.
• [Array2] is \$D\$4:\$D\$20.
• [Array3] is \$E\$4:\$E\$20. Press “Enter” to apply the criteria and get the result. Step-2:

Now, do the same for the other cells with criteria. ### 3) SUMPRODUCT with OR Logic

We can add “OR” logic to our formula to make the “SUMPRODUCT” function with criteria more dynamic.

Step-1:

Consider the situation where we need to find out the total price for “Notebook” and “Laptop”.

Create a table anywhere in the worksheet where you want to get the result. Step-2:

In cell “I4” apply the “SUMPRODUCT” function with criteria using “OR” logic. The final form of the formula is,

=SUMPRODUCT(–((B4:B20=G4)+(B4:B20=H4)>0),D4:D20,E4:E20)

Where,

• Array1 is –((B4:B20=G4)+(B4:B20=H4)>0). Here “G4” is “Notebook” and “H4” is “Laptop”. This array counts the total number of “Notebook” and “Laptop” in the data table.
• [Array2] is D4:D20.
• [Array3] is E4:E20. Press “Enter” to apply the function with criteria. We got our “Total Price”. Step-3:

You can also use the basic “SUMPRODUCT” function without the double unary operator to apply these criteria. This formula is,

=SUMPRODUCT((B4:B20=G4)+(B4:B20=H4),D4:D20,E4:E20) ### 4) SUMPRODUCT with Multiple AND/OR Criteria

In this method, we will apply the SUMPRODUCT function with criteria using both “AND”, “OR” logic.

Step-1:

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

Add these criteria anywhere in the worksheet. Step-2:

Apply the “SUMPRODUCT” function with these criteria. Insert the values and then the formula looks like,

=SUMPRODUCT(–((B4:B20=G4)+(B4:B20=H4)>0),–((C4:C20=G7)+(C4:C20=H7)>0),D4:D20,E4:E20)

Where,

• Array1 is –((B4:B20=G4)+(B4:B20=H4)>0),–((C4:C20=G7)+(C4:C20=H7)>0). Here B4:B20 is the “Product” Column, G4 and H4 are “Notebook” and “Laptop”. Similarly, C4:C20 is the “Country” column, G5 and H5 are “India” and “China”.
• [Array2] is D4:D20.
• [Array3] is E4:E20. Apply these criteria with logic by pressing “Enter”. Our final result is here. Step-3:

We can also use the basic SUMPRODUCT function without the double unary operator to apply these criteria. This formula is,

=SUMPRODUCT(((B4:B20=G4)+(B4:B20=H4))*((C4:C20=G7)+(C4:C20=H7)),D4:D20,E4:E20) ### 5) 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.

Step-1:

In the following example, we can see the price of some “Products” from the country “India”, “China”, “Italy”, “Germany”, “France”. Step-2:

We need to find the “Total Price” of the product “Laptop” from the country “China”. Now, insert these criteria anywhere in the cell. Follow the screenshot. Step-3:

Apply these criteria to the “SUMPRODUCT” function. The final function looks like

=SUMPRODUCT(C4:G7*(C3:G3=C10)*(B4:B7=C11))

Where,

• Data is C4:G7.
• Range1 is C3:G3.
• Criteria1 is
• Range2 is B4:B7.
• Criteria2 is Apply these criteria by pressing “Enter”. We have got our result. ## 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.

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