How to Use SUMPRODUCT with IF Function in Excel

Ever feel the necessity to use SUMPRODUCT combined with conditionals? Exactly the way we use SUMIF. Though SUMPRODUCT has built-in unique functionality to use conditionals, Excel does not have any formula where SUMPRODUCT and IF are combined. Yet, while analyzing data, we kept searching for it. Why not? The need is real! Let me tell you one thing – you can actually get the flexibility of SUMIF and the diverse functionality of SUMPRODUCT. All you need to do is just embed the IF conditionals directly inside the SUMPRODUCT.

To start the SUMPRODUCT combining with IF, we can start with a simple approach. We can first finalize the conditions that need to be passed inside the IF. After that, we can just pass the result into the SUMPRODUCT by wrapping it together.

Keeping this method basic, we will find sales of only January located in the region ‘East’ from this dataset.

Steps:

➤ Identify the column that contains the conditions and the range. Here, our condition is in column A (Region).
➤ Find the resulting column of sales, which is here, column C.
➤ Select a cell to display the output.
➤ In the cell, at first write the IF –

Here, the condition ‘East’ is between A2 to A11, which is passed inside the formula as the first parameter. The resulting column range of C is passed afterwards.

➤ Wrap the IF function inside the SUMPRODUCT-

Here, the IF returns true for all the cells of C2 to C11 that have ‘East’ in the column range of A. All the times it returns true, the cell values are added together.

➤ Press Enter to get the result in the cell.

In real-world problems, we rarely filter based on one condition. The datasets diversify with multiple criteria as you look at the practical work life. This is where you are left with no option but to use SUMPRODUCT. However, combining IF with them can give you versatile solutions.

With this method, we will find the total sales of Product ‘A’ in January in the ‘East’ region.

Steps:

➤ Identify the column and its ranges that have the conditions. Here, the conditions are ‘East’ and  ‘A’ in columns A and B.
➤ The result column remains the same as before (column C).
➤ Select a cell to write the formula. Give it a proper header.
➤ Build the IF condition –

This IF returns the cells of column C only when both the ‘East’ and ‘A’ are present in the range of A2 to A11 and B2 to B11.

➤ Use the IF inside the SUMPRODUCT –

➤ Press Enter to display the result.

As you have seen in AND logic, both conditions must be satisfied to produce results. However, we do not always need to fulfill all of the criteria. Sometimes we want to calculate when either one of the criteria is matched.  In that case, using OR logic in the IF is better.

To illustrate the OR logic, we will find the total sales of January in the region ‘West’ or Product ‘B’.

Steps:

➤ Decide the ranges and columns that contain the conditions. The condition ‘West’ is in column A (Region), and ‘B’ is in column B (Product).
➤ The column that needs to be summed is column C (Jan Sales).
➤ Select a cell to show the output.
➤ First, write the IF formula combining two conditions in OR logic.

Here, the cells from C2 to C11 are returned when the selected range has either one of the conditions.

➤ Use the SUMPRODUCT with the previous IF formula –

➤ Press Enter to display the result.

As with any other condition, setting numeric conditions inside the IF is possible. This enables you to use new areas of SUMPRODUCT and work diversely with your worksheet.

Here, we will find the total sales for January for the products that have sold more than 10 units.

Steps:

➤ Identify the columns that include the conditions. Here, the condition of 10 units belongs to column D (Quantity).
➤ Locate the column range of the resulting column.
➤ Select a cell to get the result of the formula.
➤ Write the IF condition for this –

This filters all the cells between C2 to C11 that has value greater than 10 in the range E2:E11.

➤ Wrap the formula of IF by SUMPRODUCT.

It sums all the values of column C cells that satisfy the condition.

➤ Press Enter to generate the result in the selected cell.

Rarely does sales data finish up in one column. In real-world datasets, columns like sales can extend to monthly, quarterly, or annual. This makes the procedure complex, as we need to repeat the same procedure with all the other datasets. Luckily, you do not need to repeat the formula time and again. Using SUMPRODUCT+IF can sort this out, too.

For this example, let’s find out the total sales for January and February in the ‘West’ region.

Steps:

➤ Find the column that contains the condition you want to meet.
➤ Select the cell to write the output.
➤ Start with the IF condition. Use the same syntax.

The range C2 to D11 is true for the cells with ‘West’ in column A.

➤ Use the SUMPRODUCT with the IF function

➤ Press  Enter to get the result.

As a single condition is not enough, single results might also not be enough. Many times, we want to get one result if one condition is fulfilled, and again another result for other conditions. It’s more like determining which columns to sum based on the conditions. Combining SUMPRODUCT, including one IF inside another IF, known as nested IF, can do the work for you.

Here, we will find the total sales of both January and February. However, we will only calculate the sales for January when the region is ‘East’, and for the ‘West’, we will calculate the sales for February.

Steps:

➤ Identify the columns for conditions (e.g, column A).
➤ The result columns need to be determined. Here, it is columns C and D.
➤ Select a cell and give it a proper header to display the outputs.
➤ Write the IF formula for both conditions.

The first IF returns column C cells  if column A contains ‘East’. And, the second IF returns column D cells if the A column contains ‘West’.

➤ Use the SUMPRODUCT with the results of the previous function.

➤ Press Enter to display the results.

Though Excel does not contain the SUMPRODUCT+IF function in a single formula, that does not mean you can make one. You can leverage the power of VBA Macros and customize your own function to do that. It not only makes your workbook cleaner, but also reduces formula errors.

Here, you will find the total sales for January in the ‘East’ region using a user-defined VBA formula.

Steps:

➤ Go to Developer tab -> Visual Basic.

➤ In the launched VBA editor window, click on the Insert tab and select Module.

➤ Paste the following code in the blank space.

Function SUMPRODUCTIF(rngCriteria As Range, Criteria As String, rngValues As Range) As Double
    Dim i As Long
    Dim result As Double
    result = 0
    For i = 1 To rngCriteria.Count
        If rngCriteria.Cells(i, 1).Value = Criteria Then
            result = result + rngValues.Cells(i, 1).Value
        End If
    Next i
    SUMPRODUCTIF = result
End Function

➤ Save the VBA snippet by  Ctrl  +  S  and close the window.
➤ Identify the range and column that is required as the criteria (column A).
➤ Determine the result column (Jan Sales).
➤ Select a cell to display the results.
➤ Write the formula using your new VBA formula –

➤Press Enter to get the results.

Frequently Asked Questions (FAQs)

Why does SUMPRODUCT with IF sometimes require Ctrl+Shift+Enter?

In previous versions of Excel, the regular Enter did not work for SUMPRODUCT. In that case, you need to press  Ctrl  +  Shift  +  Enter  to get the result. However, for Excel 365 and Excel 2021, give a result even if Enter is pressed alone.

Can SUMPRODUCT IF return results other than sums?

SUMPRODUCT can return results other than sums. It can return average, weighted average, and counts. You might need to modify and manipulate the formula slightly in that case.

Can I use text wildcards with SUMPRODUCT IF?

Wildcards do not directly work with SUMPRODUCT IF in partial text matches. For that, you will need to wrap the condition with SEARCH or RIGHT/LEFT functions combined with IF.

Why is my SUMPRODUCT IF formula returning blank or wrong results?

SUMPRODUCT IF can give blank and wrong results due to problems with either of the two formulas. Mismatched ranges are the common cause of such an issue for the SUMPRODUCT. On the other hand, IF can return wrong values if numeric and text conditions are mixed up.

Is there a performance risk in using multiple IFs inside SUMPRODUCT?

Combining SUMPRODUCT with IF can significantly slow down the performance. It mainly happens in the large workbooks. In that case, it is recommended to use Power Query or a VBA approach.

Concluding Words

SUMPRODUCT combined with IF is one of the powerful assets of Excel. From filtering by single conditions to combining multiple conditions with multiple logics of AND/OR, handling numeric conditions, and even summing multiple columns at once, SUMPRODUCT and IF give you all the flexibility. Again, for greater control, there are always customized methods and functions like VBA Macros.  As for now, you know multiple paths to the same goal, so you can mix and match and get the best blend of solutions in your worksheet.