SUMPRODUCT Excel #DIV/0! Error: Causes and Fixes

Yes—SUMPRODUCT returns #DIV/0! when its calculation divides a value by zero, or when an input cell already contains that error. Check the denominator cells and any divisions inside the formula; correct or deliberately handle zero denominators before using IFERROR to hide remaining errors.

What the SUMPRODUCT #DIV/0! error means

SUMPRODUCT multiplies corresponding values in one or more arrays and adds the products. A straightforward SUMPRODUCT calculation does not divide, so if it returns #DIV/0!, division is usually happening somewhere in the formula or in a cell the formula uses. The error means Excel encountered a division whose denominator evaluated to zero.

The division may be visible in the SUMPRODUCT formula itself, or hidden inside a referenced cell. A blank denominator can also behave like zero in an arithmetic calculation. Meanwhile, if a source cell already displays #DIV/0!, that error can flow into SUMPRODUCT even when the SUMPRODUCT formula contains no division.

This is different from a general mismatch between array sizes. Incompatible array dimensions commonly produce #VALUE!, not #DIV/0!. Identifying which operation created the error is more useful than changing SUMPRODUCT’s range sizes without evidence.

How SUMPRODUCT handles division

When division appears inside SUMPRODUCT, Excel calculates the division for the relevant array elements before it can finish the multiplication and addition. One zero denominator is enough to create an error in that calculation. SUMPRODUCT does not automatically skip the row just because its other values are valid.

For example, assume A2:A4 contains sales of 120, 80, and 90, while B2:B4 contains quantities of 10, 0, and 9. The formula below attempts to divide each sale by its matching quantity and add the resulting rates.

With sales in A2:A4 and quantities in B2:B4, enter:

=SUMPRODUCT(A2:A4/B2:B4)

This returns #DIV/0! because the second quantity is zero. The other rows produce 12 and 10, but those valid results do not cancel out the division error in the middle row. The formula is asking for a sum of per-row rates, not a single overall rate.

By contrast, SUMPRODUCT can multiply corresponding arrays without dividing. With the same values—sales in A2:A4 and quantities in B2:B4—the formula below returns 2,010: 120 × 10, plus 80 × 0, plus 90 × 9.

With sales in A2:A4 and quantities in B2:B4, enter:

=SUMPRODUCT(A2:A4,B2:B4)

This comparison helps narrow down the cause. If a multiplication-only SUMPRODUCT works but a version involving division fails, inspect the denominator values and formulas first.

Find the zero denominator

Start by inspecting the formula bar and identifying every division sign in the SUMPRODUCT formula. For each division, trace the expression after the slash back to the cells or calculations that supply its denominator. If the formula is long, evaluate its pieces separately in unused cells or use Excel’s Formula tab tools to trace its inputs.

Check each referenced denominator cell for a literal zero, a formula returning zero, or a blank.
Look for formulas that return zero when data is missing, such as a calculation that uses an IF condition.
Check whether source cells already show #DIV/0! by selecting them and inspecting their own formulas.
Confirm that the formula points to the intended rows. A shifted range can pair a valid numerator with an unrelated zero denominator.
Use Formulas > Trace Precedents to follow direct references, then inspect the source cells themselves if the chain continues through other formulas.

For a simple range, filtering or sorting the denominator column can make zeros easier to spot. Be cautious with blanks: a cell that looks empty may contain a formula returning an empty string, while a truly blank cell may be treated as zero in arithmetic. These cases can behave differently depending on the expression, so inspect the cell contents rather than relying only on what appears on the sheet.

Common reasons the denominator becomes zero

A visible zero is only one possible cause. A formula may produce zero from its inputs, a denominator may reference the wrong cell, or data imported from another source may contain unexpected blanks. A division by zero can also be inside a helper formula whose result is then included in SUMPRODUCT.

Zero is valid data: A quantity, hours total, or count can legitimately be zero. The calculation needs a defined rule for what to do in that case.
Data is missing: Blank input may be mistaken for a meaningful zero, or a formula may convert missing information to zero.
Reference error: A copied formula may have a relative reference that shifted to the wrong row or column.
Upstream error: A source calculation may already return #DIV/0!, and SUMPRODUCT carries that error forward.
Unexpected data type: Text in a calculation can cause other errors, such as #VALUE!, rather than #DIV/0!. If the displayed error is specifically #DIV/0!, focus first on zero-valued denominators or existing error cells.

Do not assume that every zero should be removed. For example, a zero quantity may mean a rate is undefined, while in a different business rule it may mean that the row should contribute nothing. The appropriate fix depends on what the result is supposed to represent.

Handle zero denominators with IF

If the intended rule is to give a zero contribution for rows whose denominator is zero, use IF to test the denominator before performing the division. With sales in A2:A4 and quantities in B2:B4, the following formula returns 22: it adds 12 for the first row, zero for the second, and 10 for the third.

With sales in A2:A4 and quantities in B2:B4, and with zero quantities intended to contribute zero, enter:

=SUMPRODUCT(IF(B2:B4=0,0,A2:A4/B2:B4))

This changes the calculation’s meaning deliberately: it treats zero-denominator rows as zero contributions. It does not calculate an average rate, and it should not be used if a zero quantity means the data is incomplete and needs review. In Microsoft 365 for Windows, enter the formula normally; SUMPRODUCT handles the array calculation without requiring Ctrl+Shift+Enter. Excel for Mac uses the same formula syntax and also supports ordinary entry in current Microsoft 365 versions.

If blank cells and zeroes should follow different rules, test for blanks separately and decide what each case means before calculating. A formula that silently treats both as zero may make a report look complete when important data is actually missing.

When IFERROR is appropriate

IFERROR can replace any error in an expression with a value you choose. It is useful when the workbook has a clear fallback rule, but it does not repair the underlying cause. Wrapping the entire calculation may hide errors other than division by zero, including problems that would otherwise reveal bad source data.

With sales in A2:A4 and quantities in B2:B4, and with zero chosen as the fallback for any error in the calculation, enter:

=IFERROR(SUMPRODUCT(A2:A4/B2:B4),0)

This returns 0 instead of #DIV/0! for the example. That result is not the sum of the valid rates; it is simply the chosen fallback. If you need to preserve valid rows while ignoring only zero denominators, use a conditional calculation such as the IF approach above, and separately investigate unexpected source errors.

In reports, a blank or a label such as “Check input” may be a more honest fallback than zero. A displayed zero can be mistaken for a real measured result. Choose a fallback that is understandable to the people who will use the worksheet.

Check the SUMPRODUCT formula and its ranges

After locating the error, verify that each range covers the intended records and that arrays used together have compatible dimensions. For example, a numerator covering rows 2 through 10 should normally be paired with the corresponding denominator rows, not a range shifted down by one row. A dimension mismatch has its own symptoms, but a mistaken pairing can still expose a zero denominator.

Also check parentheses and the order of operations in complex expressions. If division is part of an array expression, confirm that it occurs only where intended. SUMPRODUCT can perform conditional calculations, but multiplying a condition by a division is not a reliable way to protect against zero: Excel may still evaluate the division and produce an error. Put the zero check in an IF expression that controls whether the division is used.

For a quick diagnosis, compare the original calculation with a temporary version that contains only the multiplication or addition portions. If the simpler calculation works, add the divisions back one at a time. This narrows the search without changing the source data or masking errors across the whole workbook.

Prevent the error in future worksheets

Good spreadsheet design makes zero handling explicit. Decide whether zero denominators should be excluded, counted as zero contributions, displayed as a warning, or treated as invalid input. Document that rule near the calculation so a later editor does not replace it with a blanket error suppressor.

Validate inputs where practical, especially when a denominator must be positive for the calculation to make sense.
Keep source data separate from calculated results so unexpected zeros and errors are easier to inspect.
Use consistent row ranges and verify copied formulas after filling them down or across.
Test formulas with ordinary values, a zero denominator, a blank, and an existing error value.
Use a visible warning or review column when excluding a row could affect an important total.

These checks matter most in summaries used for decisions. A formula that suppresses a visible error can make a workbook appear correct while omitting a meaningful data problem. Error handling should explain the rule, not merely make the error disappear.

Frequently Asked Questions

Can SUMPRODUCT itself cause #DIV/0!?
SUMPRODUCT can return the error, but the usual cause is a division within its expression or an input cell that already contains #DIV/0!. A basic multiplication-and-addition calculation has no division by itself.

Does a blank cell count as zero in SUMPRODUCT?
In arithmetic, a blank referenced as a denominator can behave like zero and cause #DIV/0!. A formula returning an empty string is not identical to a truly blank cell, so inspect the source expression when the result is unexpected.

Should I use IFERROR to fix the error?
Use IFERROR only when replacing any error with the selected fallback is an intentional rule. If you need to keep valid rows and handle zero denominators specifically, test the denominator with IF instead.

Why does SUMPRODUCT still show an error when I multiply by a condition?
Multiplication by a false or zero condition does not reliably prevent Excel from evaluating a division elsewhere in the expression. Use IF to control whether division occurs, rather than relying on a multiplied condition to suppress the error.

Do I need Ctrl+Shift+Enter for SUMPRODUCT in Microsoft 365?
No. In current Microsoft 365 Excel, enter SUMPRODUCT formulas normally. The same applies to current Microsoft 365 for Mac; formula argument separators may depend on regional settings, but the function’s calculation is the same.

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.