# How to use SUMPRODUCT IF in Excel

SUMPRODUCT is an extremely resourceful function with many purposes. When you are comparing data between two or more ranges and calculating with multiple criteria, The SUMPRODUCT function is your first choice. It has the unique ability to handle arrays in smart and elegant ways. Often we need to use SUMPRODUCT IF or conditional SUMPRODUCT to compare between columns with given criteria and to find the result. Today in this article, we will discuss the SUMPRODUCT IF function and some other alternative approaches to SUMPRODUCT IF function.

## 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
• [ array2], [array3]â€¦: Array arguments 2 to 255 whose values you want to multiply and then add.

## SUMPRODUCT IF Formula: 2 Types

In Excel, there is no built-in â€śSUMPRODUCT IFâ€ť function but you can use this as an array formula by combining the â€śSUMPRODUCTâ€ť and the â€śIFâ€ť function. Letâ€™s discuss this formula.

### 1.Â Â  SUMPRODUCT IF with One Criteria

We can use the â€śSUMPRODUCT IFâ€ť formula with one criterion. Follow these steps to learn

Step-1:

Consider a data table where some fruit Items are given with â€śRegionâ€ť, â€śQtyâ€ť, and â€śPriceâ€ť. We will find out the total price of some items.

Step-2:

Create another table anywhere in the worksheet where you want to get the total price of the item. We choose â€śCherryâ€ť, â€śAppleâ€ť, â€śPlumâ€ť items.

Step-3:

Apply the â€śSUMPRODUCT IFâ€ť formula in cell â€śH4â€ť. The format of this formula is

=SUMPRODUCT(IF(criteria range=criteria, values range1*values range2))

Insert the values into the formula.

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

Where,

• Criteria_range is \$C\$4:\$C\$20.
• The Criteria is
• Values_range1 is \$D\$4:\$D\$20.
• Values_range2 is \$E\$4:\$E\$20.

Apply this formula as an array formula by pressing â€śCTRL+SHIFT+ENTERâ€ť simultaneously.

Step-4:

We got our total price. Now apply the same formula for the rest of the items.

### 2. SUMPRODUCT IF with Multiple Criteria in Different Column

We will use the same formula for multiple criteria.

Step-1:

Letâ€™s add another criteria â€śRegionâ€ť in table 2. In this case, we want to find the total price of â€śCherryâ€ť from the â€śNorth Americaâ€ť region and â€śAppleâ€ť from the â€śAsiaâ€ť region.

Step-2:

Now apply the â€śSUMPRODUCT IFâ€ť formula. Insert the values into the formula.

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

Where,

• Criteria_range is \$B\$4:\$B\$20, \$C\$4:\$C\$20.
• The Criteria is G4, H4.
• Values_range1 is \$D\$4:\$D\$20.
• Values_range2 is \$E\$4:\$E\$20.

Apply this formula by pressing â€śCTRL+SHIFT+ENTERâ€ť simultaneously.

Step-3:

Our value is here. Now do the same for the â€śAppleâ€ť item.

## Alternative approaches to SUMPRODUCT IF Formula

There are some other approaches to derive the previous results. An alternative way to calculate the â€śSUMPRODUCT IFâ€ť formula is to insert the criteria within the â€śSUMPRODUCTâ€ť function as an array using double unary (â€“) to converts the â€śTRUEâ€ť or â€śFALSEâ€ť into â€ś1â€ť or â€ś0â€ť.

### 1. SUMPRODUCT If with One Condition

Step-1:

We will consider the previous example in this case. We will find the total price of â€śMangoâ€ť from the list.

Step-2:

Apply the conditional â€śSUMPRODUCTâ€ť formula

The final formula is

=SUMPRODUCT(â€“(C4:C20=G4),D4:D20,E4:E20)

Where,

• Array1 is (â€“(C4:C20=G4).
• [Array2] is D4:D20.
• [Array3] is E4:E20.

Press â€śEnterâ€ť. Our result is here.

Explanation:

Â We will now explain how this conditional â€śSUMPRODUCTâ€ť function works

Step-1:

When we enter the â€śâ€“(C4:C20=G4)â€ť into the formula this double unary (â€“) converts the â€śTRUEâ€ť or â€śFALSEâ€ť into â€ś1â€ť or â€ś0â€ť. Select this â€śâ€“(C4:C20=G4)â€ť portion in your worksheet and press â€śF9â€ť to see the underlying values.

{0,1,0,0,0,0,0,0,1,1,0,1,1,0,0,0,1}

Step-2:

Now if we break down the arrays into values the actual formula will look like this,

=SUMPRODUCT({0,1,0,0,0,0,0,0,1,1,0,1,1,0,0,0,1},{20,50,100,50,500,80,90,56,60,80,100,40,45,25,35,45,90},{15,20,2,5,12,10,15,9,20,30,5,30,25,10,10,20,15})

Step-3:

The first array will multiply with the second then the second array will multiply with the third array. Follow this picture

That is how this conditional â€śSUMPRODUCTâ€ť works.

### 2. SUMPRODUCT IF with Multiple Condition in Different Column

Step-1:

In the following example, we will find out the total price of â€śCherryâ€ť from the â€śNorth Americaâ€ť region.

Step-2:

Apply the formula. The final form of this formula is,

=SUMPRODUCT(â€“(B4:B20=G4), â€“(C4:C20=H4),D4:D20,E4:E20)

Where,

• Array1 is (â€“(C4:C20=G4),â€“(C4:C20=H4).
• [Array2] is D4:D20.
• [Array3] is E4:E20.

Press â€śEnterâ€ť. Our result is achieved.

### 3. SUMPRODUCT IF with OR Logic

We can add â€śORâ€ť logic to our formula to make this formula more dynamic.

Step-1:
Letâ€™s say, we need to get the total price of â€śMangoâ€ť and â€śCherryâ€ť from the data table.

Step-2:

Apply the â€śSUMPRODUCTâ€ť formula with â€śORâ€ť and insert the values.

The formula is

=SUMPRODUCT(â€“((C4:C20=G4)+(C4:C20=H4)>0),D4:D20,E4:E20)

Where,

• Array1 is â€“((C4:C20=G4)+(C4:C20=H4)>0). Here G4 is â€śMangoâ€ť and H4 is â€śCherryâ€ť. This array counts the total number of â€śMangoâ€ť and â€śCherryâ€ť in the data table.
• [Array2] is D4:D20.
• [Array3] is E4:E20.

Press â€śEnterâ€ť to get the total price of the products.

### 4. SUMPRODUCT IF with Multiple AND/OR Criteria

In this case, we will apply Or logic with multiple conditions.

Step-1:

In the following example, we need to find the total price of â€śCherryâ€ť and â€śMangoâ€ť from â€śAsiaâ€ť and â€śEuropeâ€ť regions.

Step-2:

To get the result we will now apply the formula with AND/OR logic. The formula is

=SUMPRODUCT(â€“((B4:B20=G4)+(B4:B20=H4)>0),â€“((C4:C20=I4)+(C4:C20=J4)>0),D4:D20,E4:E20)

Where,

• Array1 is â€“((B4:B20=G4)+(B4:B20=H4)>0),â€“((C4:C20=I4)+(C4:C20=J4)>0). Here B4:B20 is â€śRegionâ€ť Column, G4 and H4 is â€śAsiaâ€ť and â€śEuropeâ€ť.Similarly,C4:C20 is â€śItemâ€ť column, I4 and J4 is â€śCherryâ€ť and â€śMangoâ€ť.
• [Array2] is D4:D20.
• [Array3] is E4:E20.

Press â€śEnterâ€ť to get the total price.

## Quick Notes

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

âś…The â€śSUMPRODUCTâ€ť function treats non-numeric values as zeros. If you have any non-numeric values in your formula the answer will be â€ś0â€ť.

âś… Since the â€śSUMPRODUCT IFâ€ť formula is an array formula your need to press â€śCTRL+SHIFT+ENTERâ€ť simultaneously to apply the formula.

âś…The â€śSUMPRODUCTâ€ť function does not support wildcard characters.

## Conclusion

Today we discussed the â€śSUMPRODUCT IFâ€ť formula and some other alternative conditional â€śSUMPRODUCTâ€ť formula in this article. Hope this article is useful to you when you are facing problems. If you have any confusion, you are welcome to comment.