SUMPRODUCT with Criteria in Excel (5 Methods)

final result

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.

Download Practice Workbook

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

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

creating table

Step-2:

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

new table input

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.

applying formula

Press “Enter”. We have got the total price for “India”.

result

Step-4:

Now select the cell “H4” and drag to get the final result.

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.

applying formula

Press “Enter”. Our result is here.

getting result

Step-2:

Now apply the same for the rest of the cells.

Final result

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!

Read more: SUMPRODUCT Function with Multiple Columns in Excel

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.

creating table

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.

applying formulaa

Press “Enter” to apply these criteria.

getting result

Step-3:

We got our result. Now apply the same criteria for the other two cells.

final result

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.

applying formula

Press “Enter” to apply the criteria and get the result.

getting result

Step-2:

Now, do the same for the other cells with criteria.

final result

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.

Putting criteria

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.

applying formula

Press “Enter” to apply the function with criteria. We got our “Total Price”.

getting result

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)

final result

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.

putting criteria

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.

applying formula

Apply these criteria with logic by pressing “Enter”. Our final result is here.

getting result

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)

final result

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

creating table

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.

creating table

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

applying formula

Apply these criteria by pressing “Enter”. We have got our result.

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


Further Readings:

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo