SUMPRODUCT Formula Not Working? Fix the Common Causes

If SUMPRODUCT is not working in Excel, first check that every range in the formula has the same dimensions, then check the criteria, data types, and source cells for errors. A mismatched range often produces #VALUE!; a formula that calculates but returns zero usually points to a condition that matches nothing or values Excel is not treating as numbers.

SUMPRODUCT is useful for multiplying corresponding values and adding the results, or for applying several conditions in one calculation. The fixes below focus on Microsoft Excel for Microsoft 365 on Windows; the formulas also work in current Excel for Mac and Excel for the web, although menus and keyboard shortcuts can differ.

How SUMPRODUCT works

SUMPRODUCT takes one or more arrays, multiplies corresponding positions when there are multiple arrays, and adds those products. For example, it can total line-item revenue when one range contains quantities and another contains unit prices. It can also turn TRUE/FALSE tests into 1s and 0s, allowing conditions to include or exclude rows.

With quantities in B2:B6 and unit prices in C2:C6, the formula is =SUMPRODUCT(B2:B6,C2:C6). If the rows contain 2 and 10, 3 and 5, 1 and 20, 4 and 8, and 2 and 7, the result is 101: 20 + 15 + 20 + 32 + 14. The paired ranges must line up row by row; Excel does not match records by labels or reorder them for you.

SUMPRODUCT can also perform a conditional sum. With regions in A2:A6 and sales in C2:C6, the formula =SUMPRODUCT((A2:A6="East")*C2:C6) returns 85 when the East sales are 40, 15, and 30. The comparison creates TRUE/FALSE results; multiplication treats TRUE as 1 and FALSE as 0, so only matching sales contribute.

Check range dimensions first

This is the first check when Excel displays #VALUE!. Every array used together must have the same number of rows and columns. A range with five rows cannot be paired with one containing six, and a vertical range cannot be paired with a horizontal range merely because both contain the same number of cells.

Compare the first and last row in every range. For example, A2:A100 and C2:C100 have matching heights.
Check whether one range accidentally starts at row 1 while another starts at row 2.
For two-dimensional ranges, make sure both the row count and column count match.
Use a small, clearly bounded range while troubleshooting, rather than entire-column references.

For instance, if the regions are in A2:A6 and sales are in C2:C7, then =SUMPRODUCT((A2:A6="East")*C2:C7) has incompatible array sizes. Change the sales range to C2:C6 if those are the corresponding records, or extend the other range only if the extra row is genuinely part of the data. Do not fix an error by making ranges equal in size without checking that they refer to the same records.

Review the criteria and condition logic

A conditional SUMPRODUCT often returns zero because its test does not match the cell contents exactly. Text comparisons in Excel are generally not case-sensitive, but spaces and different characters still matter. A value that looks like East may contain a trailing space, a nonbreaking space copied from a website, or a slightly different spelling. Inspect the source cells, or compare a suspect cell with the criterion using a separate test.

For multiple conditions, multiply the tests so a row contributes only when all tests are TRUE. With regions in A2:A6, product names in B2:B6, and amounts in C2:C6, use =SUMPRODUCT((A2:A6="East")*(B2:B6="Widget")*C2:C6). If the five rows are East/Widget/40, West/Widget/25, East/Cable/15, East/Widget/30, and West/Cable/20, the result is 70. Only the two East Widget amounts, 40 and 30, are included.

For an either-or condition, add the tests rather than multiplying them, but avoid counting a row twice if it can satisfy both conditions. Parentheses matter: each comparison should be grouped before the multiplication or addition. Also remember that an equals test looks for a match; it does not automatically interpret a text criterion containing an asterisk as a wildcard in the same way as some criteria-based functions do.

Check numbers, text, blanks, and errors

Cells can display digits while storing text. This commonly happens after importing a CSV, pasting data from a web page, or using an apostrophe before a value. SUMPRODUCT may treat nonnumeric entries in array references as zero in a direct calculation, so numeric text can make a total smaller than expected. Check a sample cell by changing its number format or testing whether it behaves like a number; changing the display format alone does not convert text into a numeric value.

Convert imported values using Excel’s available data-cleaning tools, such as Text to Columns, or correct the source data. Avoid wrapping an entire range in a coercion technique until you have checked for words or other unexpected entries: coercion can turn valid numeric text into numbers, but may also cause errors on text that cannot be converted. Blank cells are usually not the issue in a product calculation; they contribute no numeric amount, but blanks in a criteria range will not match a nonblank criterion.

Any source error such as #N/A or #DIV/0! can flow into the result. Locate errors in each referenced range and decide whether the underlying value should be corrected or intentionally handled. Hiding errors with a blanket error-handling formula can conceal a bad input and produce a plausible but wrong total.

Handle dates and numeric criteria carefully

Excel stores genuine dates as serial numbers, even though cells display calendar dates. Text that merely looks like a date may not compare correctly. Confirm that the date cells are real Excel dates, and use explicit start-inclusive and end-exclusive limits for a year or month. This avoids missing records that include a time component after midnight on the final date.

With dates in A2:A5 and sales in B2:B5, use =SUMPRODUCT((A2:A5>=DATE(2026,1,1))*(A2:A5<DATE(2027,1,1))*B2:B5). If the dates are December 31, 2025; January 1, 2026; June 30, 2026; and January 1, 2027, and the corresponding sales are 100, 50, 75, and 20, the result is 125. The lower limit includes the start of 2026, while the exclusive upper limit excludes the start of 2027 and any later time on December 31, 2026.

For a numeric threshold, compare the range directly with the threshold cell rather than building a text criterion unnecessarily. Check that the threshold and the compared values use compatible numeric types and units. For example, 10 stored as text may not behave like the number 10 in every calculation.

Watch calculation settings and workbook size

If the inputs change but the answer does not, confirm that Excel is recalculating. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options > Automatic. A workbook set to manual calculation may need recalculation; press F9 to recalculate open workbooks. On a Mac, calculation controls are available from the Formulas tab, though menu and shortcut details can vary by keyboard settings.

Large or numerous SUMPRODUCT formulas can slow a workbook, especially when they use entire columns such as A:A. Prefer ranges sized to the actual data, or use an Excel Table so formulas can refer to structured columns that expand with new records. SUMPRODUCT is not inherently wrong for large data, but repeated array calculations across more than a million rows can be unnecessarily expensive.

When SUMIFS is a better choice

For a straightforward conditional total, SUMIFS is often easier to read and maintain. It names the sum range and each criteria range explicitly, and is designed for sums with one or more criteria. With regions in A2:A6, product names in B2:B6, and amounts in C2:C6, =SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Widget") returns 70 for the example data above.

SUMPRODUCT remains useful for calculations that combine multiplication with conditions, weighted averages, or logic that is awkward to express with SUMIFS. A weighted average is one common example: multiply each value by its weight, add those products, then divide by the total weight. With rates in B2:B4 and quantities in C2:C4, =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) returns 9.6 when the rates are 10, 12, and 8 and the quantities are 2, 3, and 5. The weighted total is 96 and the total weight is 10, so 96 divided by 10 is 9.6. If the weights sum to zero, this calculation will return a division error and the data or intended handling needs review.

Frequently Asked Questions

Why does SUMPRODUCT return #VALUE!?
Most often, ranges used together have different dimensions, or a referenced cell contains an error. Compare the range boundaries and inspect the source cells. If the dimensions match, test the formula on a smaller portion of the data to isolate the problematic row.

Why does SUMPRODUCT return 0 when matching values are visible?
Check for extra spaces, text numbers, date values stored as text, or a criterion that differs from the actual contents. Also confirm that the amount range is aligned with the criteria range. A condition can match correctly while the corresponding amount is blank or nonnumeric.

Does SUMPRODUCT need Ctrl+Shift+Enter?
No. In current Excel, SUMPRODUCT is entered normally with Enter; it is not a legacy array formula that requires Ctrl+Shift+Enter. The formula still needs correctly sized ranges and valid arguments.

Can SUMPRODUCT use whole-column references?
It can, but doing so may make a workbook slower because Excel must evaluate very large arrays. Use bounded ranges or table columns when practical, especially when many SUMPRODUCT formulas recalculate together.

Why does the formula work on one computer but not another?
Excel’s list separator depends on regional settings. The formulas here use commas, as requested for standard English-language syntax, but some installations require semicolons between arguments. If Excel rejects the formula immediately, check the local separator setting rather than changing the calculation logic.

A reliable troubleshooting order

Start with the exact symptom: an error, an unexpected zero, a result that is too high or low, or a value that does not update. Then verify that ranges align, inspect the data types and any source errors, and evaluate each condition separately on a few rows. Confirm the arithmetic with a small hand-checkable sample before applying the formula to a larger dataset. If the calculation is simply a conditional sum, compare it with SUMIFS; choosing the simpler function can make future debugging much easier.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.