SUMPRODUCT in Excel: Conditional Calculations

Formulas & functions · Updated November 18, 2026

Yes. In Excel, SUMPRODUCT can add values that meet one or more conditions by turning each condition into an array of TRUE and FALSE results, then multiplying those results by the values to sum. With regions in A2:A6, products in B2:B6, and sales in C2:C6, this formula returns 300, the total sales for East: =SUMPRODUCT(($A$2:$A$6="East")*($C$2:$C$6))

SUMPRODUCT is useful when a calculation needs several kinds of criteria or array operations in one formula. For a straightforward conditional sum, SUMIFS is often easier to read; knowing how SUMPRODUCT works helps when conditions need more flexibility.

How conditional SUMPRODUCT works

SUMPRODUCT multiplies corresponding items in arrays and adds the resulting products. In the example above, the region comparison produces TRUE for East rows and FALSE for other rows. When those logical values are used in multiplication, TRUE acts like 1 and FALSE like 0. Each sales amount is therefore either included in the total or multiplied by zero and excluded.

The sample records are East/Pen/$100, West/Pen/$80, East/Notebook/$150, East/Pen/$50, and West/Notebook/$120. The East rows total $100 + $150 + $50, or $300. The dollar signs here are just illustrative sales values; the formula sums the numeric cell contents regardless of currency formatting.

The condition is enclosed in parentheses so Excel evaluates it as an array before multiplying it by the sales range. All the ranges in a SUMPRODUCT calculation should have matching dimensions. Here, each range covers five rows, so values and conditions line up row by row.

Sum values that match multiple conditions

To require two conditions at once, multiply the condition arrays. A row contributes only when both tests are TRUE, because 1 multiplied by 1 is 1, while any product containing 0 is 0. With the same sample layout, this formula returns 150: East sales for Pen, comprising $100 and $50.

With regions in A2:A6, products in B2:B6, and sales in C2:C6, use: =SUMPRODUCT(($A$2:$A$6="East")*($B$2:$B$6="Pen")*($C$2:$C$6))

You can add more conditions in the same way, such as a date range, a status column, or a numeric threshold. Each extra condition makes the test more specific. For example, multiplying a region test by a sales-at-least-$100 test would keep qualifying East records and exclude smaller sales. When the criteria come from worksheet cells rather than typed text, use the cell reference in the comparison so a user can change the criterion without editing the formula.

For a count rather than a sum, multiply or coerce the condition array and let SUMPRODUCT add the resulting ones. With regions in A2:A6, this formula counts the three East records and returns 3: =SUMPRODUCT(--($A$2:$A$6="East"))

Use OR conditions and text matching

Multiplying conditions means AND: every condition must be met. Adding condition arrays means OR: a row can qualify by meeting either test. With products in B2:B6 and sales in C2:C6, the following totals sales for Pen or Notebook. It returns 500 because all five sample records contain one of those two products.

With products in B2:B6 and sales in C2:C6, use: =SUMPRODUCT((($B$2:$B$6="Pen")+($B$2:$B$6="Notebook"))*$C$2:$C$6)

That addition works as intended when the alternatives cannot both be true for the same row, as with a single product field containing either Pen or Notebook. If a row can match both alternatives, adding the tests can count that row twice. In that situation, design the OR test so each row contributes at most once, or use a different approach suited to the data.

For a partial text match, SEARCH can look for a word within each cell. With product descriptions in B2:B6 and sales in C2:C6, this formula includes descriptions containing “note,” without regard to letter case. For the sample product names it returns 270, adding the two Notebook sales of $150 and $120.

With descriptions in B2:B6 and values to sum in C2:C6, use: =SUMPRODUCT(--ISNUMBER(SEARCH("note",$B$2:$B$6))*$C$2:$C$6)

SEARCH returns a position when it finds the text and an error when it does not; ISNUMBER converts those outcomes into TRUE or FALSE, and the double unary operator turns the logical results into numbers. For exact text criteria, use a direct equality comparison instead. Excel’s ordinary equality comparisons are not case-sensitive, so SUMPRODUCT with an exact text test will usually treat “east” and “East” as equal.

Compare values and handle date criteria

Conditions do not have to be text comparisons. You can test whether a number is greater than, less than, or equal to a threshold, or combine those tests to create a range. For example, a sales threshold can exclude low-value rows while a region condition restricts the calculation to a particular area. In a date table, the same idea can select transactions within a start and end date, provided the cells contain real Excel dates rather than text that merely looks like a date.

When the threshold or dates are stored in cells, compare the data range with those cells. This makes the formula reusable: changing the input cell changes which records qualify. For a date interval, use a start-date test and an end-date test together, and decide whether the end date should be included. If the source data includes times as well as dates, an end-date test may need to account for the time component so late-day records are not accidentally left out.

SUMPRODUCT evaluates these tests over the ranges without requiring you to enter the formula as a legacy array formula. In current Microsoft 365 Excel, press Enter normally. This is different from older array-formula workflows that used Ctrl+Shift+Enter; that key combination is not required for an ordinary SUMPRODUCT formula.

When SUMPRODUCT is a good choice

Choose SUMPRODUCT when the calculation is more involved than a basic conditional sum, or when combining conditions and arithmetic in a single expression makes the logic clearer. It can sum products of corresponding values, count qualifying rows, and handle array calculations without a separate helper column. It is also familiar in workbooks that need to support older Excel versions.

For a basic sum with several AND criteria, SUMIFS is commonly the more readable choice. It separates the sum range from pairs of criteria ranges and criteria, making the purpose of a simple total easy to spot. SUMPRODUCT becomes more attractive when you need OR logic, a calculation applied to each row before summing, or conditions that are awkward to express with SUMIFS. There is no universal rule that one function is always faster or better; formula clarity and the size of the data both matter.

Use a small, clear formula where possible. If a calculation becomes difficult to audit, break it into helper columns or intermediate results. A workbook that another person can understand and maintain is often more useful than a compact formula with many nested operations.

Common errors and ways to avoid them

The most frequent cause of unexpected results is misaligned ranges. If the condition range covers A2:A100 but the values range covers C2:C99, the arrays do not represent the same records. Keep ranges the same size and begin and end them on the same rows. SUMPRODUCT can return an error when array dimensions do not match.

Other practical checks include:

  • Confirm that the cells being added contain numbers. Text values that look numeric may not behave as expected in calculations.
  • Check for leading or trailing spaces in text data. An exact comparison may not match a cell that contains an invisible extra space.
  • Use parentheses around each condition, especially when combining multiplication and addition. This makes the intended logic easier to inspect.
  • Test the condition by itself in a spare area or use a small subset of rows to verify which records qualify.
  • Avoid using entire-column references in complex SUMPRODUCT formulas across very large sheets. Bounded ranges can reduce unnecessary calculation work.

SUMPRODUCT generally treats text in an array differently depending on where and how it is used, so clean source data is important. If results seem wrong, check the actual cell contents, not just their displayed formatting. Also confirm whether the rule should include boundary values: a test for greater than a threshold differs from a test for greater than or equal to it.

Frequently Asked Questions

Can SUMPRODUCT use multiple criteria?
Yes. Multiply condition arrays to require all of them, then multiply the combined result by the values to sum. Each condition range must line up with the other ranges.

Is SUMPRODUCT better than SUMIFS?
Not for every task. SUMIFS is usually simpler for a standard conditional sum with AND criteria. SUMPRODUCT is useful when you need more complex array logic, OR conditions, or row-by-row arithmetic.

Do I need Ctrl+Shift+Enter for SUMPRODUCT in Microsoft 365?
No. In current Excel for Microsoft 365 on Windows, enter a normal SUMPRODUCT formula with Enter. Excel for Mac and Excel for the web also support SUMPRODUCT; ribbon and keyboard details differ across platforms, but this calculation does not need a special array-entry shortcut.

Can SUMPRODUCT match part of a text string?
Yes. A common method combines SEARCH with ISNUMBER to identify cells containing a phrase, then uses those results to include matching values. SEARCH is not case-sensitive; use a different text-search approach if case-sensitive matching is essential.

Why does my SUMPRODUCT formula return zero?
Check whether any rows actually meet every condition, whether text criteria match the cell contents, and whether numeric values are stored as numbers. Also verify that comparison operators and boundary rules reflect the result you want.

Practical takeaway

For conditional calculations, SUMPRODUCT works by evaluating matching arrays and summing the values that survive the conditions. Start with exact, aligned ranges; use multiplication for AND and carefully designed addition for OR; and compare the result with a few manually checked rows. For a simple conditional total, consider SUMIFS for readability. For more flexible logic, SUMPRODUCT provides a compact tool—provided the conditions and array sizes are easy to verify.

Related

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.