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 combination 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 combined function and some other alternative approaches to this combination.
Download Practice Workbook
Download this practice sheet to practice the task while you are reading this article.
Introduction to SUMPRODUCT Function in Excel
Technically, the SUMPRODUCT function returns the sum of the values of corresponding arrays or ranges. Normally, multiplication is the default operation, but division, subtraction, or addition 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 values we want to multiply, and then add.
- [ array2], [array3]…: Array arguments 2 to 255 whose values we want to multiply, and then add.
2 Examples of Using SUMPRODUCT IF Combination in Excel
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 functions. Let’s discuss this formula.
Example 1: Apply SUMPRODUCT IF Formula with One Criteria
We can use this 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 following 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$5:$C$21=G5,$D$5:$D$21*$E$5:$E$21))
Where,
- Criteria_range is $C$5:$C$21.
- The Criteria are G5, G6 and G7.
- Values_range1 is $D$5:$D$21.
- Values_range2 is $E$5:$E$21.
- Apply this formula as an array formula by pressing CTRL+SHIFT+ENTER simultaneously. If you are using Excel 365, you can press just ENTER to apply an array formula.
Step-4:
- We got our total price. Now apply the same formula for the rest of the items.
Read More: How to Use SUMPRODUCT with Criteria in Excel (5 Methods)
Example 2: Apply SUMPRODUCT IF Formula with Multiple Criteria in Different Columns
We will use the same formula for multiple criteria.
Step-1:
- Let’s add another criterion “Region” in table 2. In this case, we want to find the total price of “Cherry” from the “Oceania” region and “Apple” from the “Asia” region.
Step-2:
- Now apply the formula below. Insert the values into the formula.
=SUMPRODUCT(IF($B$5:$B$21=G5,IF($C$5:$C$21=H5,$D$5:$D$21*$E$5:$E$21)))
Where,
- Criteria_range is $B$5:$B$21, $C$5:$C$21.
- The Criteria is G5, H5.
- Values_range1 is $D$5:$D$21.
- Values_range2 is $E$5:$E$21.
- Now, press ENTER.
Step-3:
- Our value is here. Now do the same for the “Apple” item.
Read More: How to Use SUMPRODUCT IF in Excel (2 Suitable Examples)
Similar Readings
- How to Use MAX IF Function in Excel (4 Practical Examples)
- Example with Excel Solver to Minimize Cost
- How to Use Excel IF Function with Range of Values (10 Examples)
How to Use Only SUMPRODUCT Instead of SUMPRODUCT IF Formula in Excel
There are some other approaches to deriving the previous results. An alternative way to insert the criteria within the SUMPRODUCT function as an array using double unary (–) to convert the TRUE or FALSE into 1 or 0.
SUMPRODUCT with One Condition:
We will consider the previous example in this case. We will find the total price of “Mango” from the list.
- Apply the conditional SUMPRODUCT formula below.
=SUMPRODUCT(--(C5:C21=G5),D5:D21,E5:E21)
Where,
- Array1 is (–(C5:C21=G5).
- [Array2] is D5:D21.
- [Array3] is E5:E21.
- Press “Enter”. Our result is here.
Formula Explanation:
We will now explain how this conditional SUMPRODUCT function works
- 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.
Output: {0,1,0,0,0,0,0,0,1,1,0,1,1,0,0,0,1}
- 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})
- 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.
Applying Multiple Conditions in Different Columns:
In the following example, we will find out the total price of “Cherry” from the “Oceania” region.
- Apply the formula. The final form of this formula is,
=SUMPRODUCT(--(B5:B21=G5), --(C5:C21=H5),D5:D21,E5:E21)
Where,
- Array1 is (–(C5:C21=G5),–(C5:C21=H5).
- [Array2] is D5:D21.
- [Array3] is E5:E21.
- Press ENTER. Our result is achieved.
Read More: SUMPRODUCT Function with Multiple Columns in Excel (4 Ways)
Applying OR Logic:
We can add OR logic to our formula to make this formula more dynamic.
Let’s say, we need to get the total price of “Mango” and “Cherry” from the data table.
- Apply the SUMPRODUCT formula with OR and insert the values.
- The formula is
=SUMPRODUCT(--((C5:C21=G5)+(C5:C21=H5)>0),D5:D21,E5:E21)
Where,
- Array1 is –((C5:C21=G5)+(C5:C21=H5)>0). Here G5 is “Mango” and H5 is “Cherry”. This array counts the total number of “Mango” and “Cherry” in the data table.
- [Array2] is D5:D21.
- [Array3] is E5:E21.
- Press “Enter” to get the total price of the products.
Applying Multiple AND/OR Criteria:
In this case, we will apply Or logic with multiple conditions.
In the following example, we need to find the total price of “Cherry” and “Mango” from “Asia” and “Europe” regions.
- To get the result we will now apply the formula with AND/OR logic. The formula is
=SUMPRODUCT(--((B5:B21=H4)+(B5:B21=H5)>0),--((C5:C21=H6)+(C5:C21=H7)>0),D5:D21,E5:E21)
Where,
- Array1 is –((B5:B21=H4)+(B5:B21=H5)>0),–((C5:C21=H6)+(C5:C21=H7)>0). Here B5:B21 is “Region” Column, H4 and H5 is “Asia” and “Europe”.Similarly,C5:C21 is “Item” column, H6 and H7 is “Cherry” and “Mango”.
- [Array2] is D5:D21.
- [Array3] is E5:E21.
- 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 formulaS 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. To learn more about Excel, please visit our site.