SUMPRODUCT IF Function: How to Sum with Conditions

No, Excel does not have a separate SUMPRODUCTIF or “SUMPRODUCT IF” function. To sum values that meet conditions, use SUMPRODUCT with logical tests, or use SUMIFS when it fits your needs; IF can also be nested inside SUMPRODUCT, but it is usually unnecessary.

What “SUMPRODUCT IF” Usually Means

People searching for a “SUMPRODUCT IF function” generally want to add numbers only when one or more conditions are true. For example, they might want total sales for one region and one product, or total expenses above a certain amount. Excel has no function with the exact name SUMPRODUCTIF. Instead, SUMPRODUCT can multiply a set of condition tests by the values to be added.

Here is a small example table. Assume A2:A6 contains regions, B2:B6 contains products, and C2:C6 contains sales:

East, Apples, 120
West, Apples, 90
East, Pears, 60
East, Apples, 80
West, Pears, 50

With regions in A2:A6, products in B2:B6, and sales in C2:C6, this formula totals East-region sales for Apples and returns 200:

=SUMPRODUCT((A2:A6="East")*(B2:B6="Apples")*C2:C6)

The result is 200 because the two matching rows have sales of 120 and 80. Each condition produces TRUE or FALSE values; when multiplied, TRUE behaves like 1 and FALSE like 0. Multiplying those results by sales leaves qualifying amounts in the calculation and makes nonmatching amounts contribute zero.

How the SUMPRODUCT Conditions Work

SUMPRODUCT multiplies corresponding items in arrays and adds the products. In a conditional sum, the comparison tests act as filters. Excel evaluates a test such as A2:A6="East" across the range, producing a series of TRUE and FALSE results. Arithmetic inside the formula coerces those results into 1s and 0s.

With regions in A2:A6 and sales in C2:C6, this one-condition formula returns 260, adding the sales for every East row:

=SUMPRODUCT((A2:A6="East")*C2:C6)

The East amounts are 120, 60, and 80, which total 260. To add another required condition, multiply by another test, as in the Apples example above. This is an AND operation: a row contributes only when both tests are true. You can add further condition arrays as needed, provided the ranges have matching dimensions.

Because each comparison is evaluated across the range, you normally do not need to enter a SUMPRODUCT formula as a legacy array formula. In Microsoft 365 for Windows, press Enter after entering it. The same formula approach is available in Excel for Mac; menu names or keyboard shortcuts are not needed for this calculation.

Using IF Inside SUMPRODUCT

IF can explicitly return a value when conditions are met and zero otherwise. That can make a formula more familiar if you already think of the task as “if this row qualifies, include its sales.” With regions in A2:A6, products in B2:B6, and sales in C2:C6, this formula also returns 200:

=SUMPRODUCT(IF((A2:A6="East")*(B2:B6="Apples"),C2:C6,0))

The IF expression produces sales for rows meeting both tests and zero for other rows; SUMPRODUCT then adds the resulting values. For this straightforward task, the version that multiplies the two tests by C2:C6 is shorter and commonly easier to maintain. Nesting IF is not what turns SUMPRODUCT into a separate conditional-sum function.

Use the IF version when its true/false branches make a more complicated calculation clearer, not simply because the word “if” appears in the request. When building formulas, verify that the condition ranges and the values-to-sum range cover the same rows.

When SUMIFS Is the Better Choice

For a standard conditional sum, SUMIFS is often the clearest choice. It is specifically designed to sum one range based on one or more criteria. With regions in A2:A6, products in B2:B6, and sales in C2:C6, the following formula returns the same 200 as the two-condition SUMPRODUCT example:

=SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Apples")

The first argument is the sum range. The remaining arguments come in pairs: a criteria range, then the criterion to apply to it. SUMIFS is typically easier to read when the goal is simply to sum rows matching criteria, and it supports criteria such as comparisons and wildcard patterns.

Choose SUMPRODUCT when the calculation needs array operations, conditions that are awkward to express as ordinary SUMIFS criteria, or compatibility with a formula design that already uses array arithmetic. Choose SUMIFS when it expresses the task plainly. Neither choice is a “SUMPRODUCT IF” function; both are real Excel functions that can solve conditional-sum problems.

OR Conditions and More Flexible Tests

Multiplying tests means all of them must pass. For an OR condition, add the tests instead. If regions are in A2:A6 and sales are in C2:C6, this formula totals sales for either East or West and returns 400 for the example table:

=SUMPRODUCT(((A2:A6="East")+(A2:A6="West")>0)*C2:C6)

The test sum is greater than zero if either comparison is true. The greater-than-zero check is useful when criteria might overlap: it turns the result back into a single 1 rather than potentially counting a row twice. In this example the two region labels are mutually exclusive, and all five sales add up to 400.

Conditions can also compare numbers. For example, a test such as a quantity range being greater than 10 can be multiplied by another test or by a values-to-sum range. Text tests such as exact matches are case-insensitive in ordinary Excel comparisons. If you need case-sensitive matching or more involved text logic, the formula design needs additional functions and should be checked against the actual data.

For date-based criteria, use real Excel date values in the cells and compare them with a date or a cell containing a date. Dates stored as text can behave differently from actual dates, so check the underlying values if a result looks wrong. For a simple date range, SUMIFS is often the more readable option.

Common Errors and How to Check Them

Conditional SUMPRODUCT formulas are compact, but several common issues can produce an error or an unexpected total:

Range sizes do not match. The region, product, and sales ranges should cover the same rows. A2:A6 paired with C2:C7 is not a consistent set of arrays.
Numbers are stored as text. Text that looks like a number may not behave as a numeric amount in calculations. Check the source cells and convert them to numbers if appropriate.
Criteria do not match the data. Extra spaces, spelling differences, and inconsistent labels can make an exact text test return FALSE. Check a few source cells or clean the data.
Blank or error cells affect results. A source error in an evaluated range can cause the formula to return an error. Identify and address the underlying error rather than masking it without understanding why it exists.
Wildcards are assumed to work in an equality test. A comparison like A2:A6="East*" treats the asterisk as a character, not as a wildcard pattern. Use SUMIFS for wildcard criteria, or choose a suitable text-search approach for a SUMPRODUCT formula.

If a result seems incorrect, temporarily test one condition at a time, confirm which rows should qualify, and compare the result with a filtered view or a small hand calculation. This makes it easier to tell whether the issue is a formula structure problem or inconsistent source data.

Performance and Practical Formula Choices

On a small table, either SUMPRODUCT or SUMIFS will usually calculate quickly. On large workbooks, avoid using entire-column references in complex SUMPRODUCT formulas when a bounded range or an Excel Table column will do. SUMPRODUCT may evaluate many array elements, and repeating that work across many formulas can make a workbook slower.

Keep the formula tied to the real data area, or convert the source data to an Excel Table so references can expand as rows are added. Tables also make formulas more self-explanatory, though structured references have their own syntax. If several people maintain the workbook, a readable SUMIFS formula may be preferable to a denser array expression even when both return the same number.

Formula separators can depend on regional settings. The examples here use commas, as in the standard English-language Excel syntax; a particular installation may display or require semicolons as argument separators. That is a locale difference, not a different conditional-sum function.

Frequently Asked Questions

Is SUMPRODUCTIF a real Excel function?
No. Excel has SUMPRODUCT and other functions such as SUMIFS, but not a built-in function named SUMPRODUCTIF.

Can SUMPRODUCT use multiple conditions?
Yes. Multiply condition tests to require all conditions to be true. For example, the East-and-Apples formula above uses two tests and adds only sales from rows that meet both.

Should I use SUMPRODUCT or SUMIFS?
For ordinary conditional sums, start with SUMIFS because its criteria-range and criteria pairs are explicit. Consider SUMPRODUCT when you need array-style calculations or a more specialized condition.

Do I need Ctrl+Shift+Enter for SUMPRODUCT?
Usually not for the formulas shown here. Enter the formula normally. This differs from some older array formulas that required Ctrl+Shift+Enter.

Can I use IF with SUMPRODUCT?
Yes. An IF expression can return the values to include and zero for the rest. For a simple conditional sum, multiplying the condition arrays by the sum range is usually shorter.

The Practical Answer

There is no dedicated “SUMPRODUCT IF” function in Excel, but you can use SUMPRODUCT with condition arrays to sum matching rows, and you can nest IF when explicit true and false results help. For straightforward criteria, SUMIFS is often the simplest option. Pick the formula that makes the conditions clear, keep the ranges aligned, and validate the result against a few rows of data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.