Fixing Excel SUMPRODUCT That Returns Wrong Total When Arrays Contain Text

You built a SUMPRODUCT formula that looks perfect on screen, but the total is off — sometimes by a little, sometimes dramatically — and there's no red error cell anywhere to tip you off. The culprit is almost always text hiding in one of your arrays, and SUMPRODUCT's default behavior is to silently treat those values as zero.

This guide walks you through exactly why that happens, how to catch it early, and four different repair strategies depending on how your data is structured.

What You'll Learn

• How SUMPRODUCT evaluates arrays and what happens when types are mixed
• How to detect text masquerading as numbers in your source data
• Four formula patterns that correctly handle text in arrays
• How to combine text-handling with error-handling in one formula
• Common mistakes that make the problem worse or harder to find

What's Actually Happening Inside SUMPRODUCT

SUMPRODUCT takes two or more arrays, multiplies corresponding elements together, then sums the products. With clean numeric data it's reliable and fast. The basic form looks like this:

=SUMPRODUCT(A2:A10, B2:B10)

Internally, Excel evaluates each pair of cells and expects both to be numeric. When it hits a text value during multiplication, the result of that element pair is treated as zero — not an error, just zero. That silent zero gets added to the running total, so the grand total comes out lower than it should be, and you get no warning at all.

This is different from how a regular SUM behaves. SUM simply skips non-numeric cells. SUMPRODUCT doesn't skip — it multiplies the text cell against its partner and produces zero for that row, which is subtly worse because it can also nullify a valid numeric value in the paired array.

Why Text in an Array Silently Breaks the Total

Text ends up in numeric columns more often than you'd expect. The most common sources are:

• Data exported from another system where numbers arrive as strings (a leading apostrophe or space is a giveaway)
• Cells formatted as Text before values were entered, so Excel stored them as strings even though they look numeric
• Manual entries like "N/A", "TBD", or blank strings "" used as placeholders
• Merged or imported data where some rows have units appended — "150 kg" instead of 150

The insidious part is that the cell may display a number. A value stored as text and a value stored as a number look identical unless you check cell alignment (text left-aligns by default), run ISNUMBER, or look for the small green triangle Excel sometimes shows in the corner.

If you're also seeing zero-result issues with other aggregation functions, the pattern of text-formatted numbers causing silent failures is the same one behind AVERAGEIFS returning zero when criteria unexpectedly exclude rows — worth reading alongside this guide.

How to Spot the Problem Before It Bites You

Before you fix the formula, confirm text is actually the cause. Run a quick diagnostic by checking one of your arrays with ISNUMBER:

=SUMPRODUCT(--(ISNUMBER(A2:A10)))

That formula counts how many cells in the range are genuinely numeric. If the count is less than the number of rows in your range, you have text or blanks mixed in. Compare that count against COUNTA(A2:A10) — any gap is a text or error value.

You can also use a conditional format on the suspect column with this formula rule: =NOT(ISNUMBER(A2)). Excel will highlight every non-numeric cell in red, making it immediately obvious where the contamination is.

Fix 1: Coerce Text to Numbers With Double Negative (--)

The double negative is the most widely used fix and works when the text looks like a number — meaning it can actually be converted. It forces Excel to treat each array value as a numeric type before multiplication happens.

=SUMPRODUCT(--(A2:A10), --(B2:B10))

The first - converts the value to a negative number (or fails to zero if it truly can't be parsed). The second - flips it back to positive. For a value like "150" stored as text, this coercion works perfectly. For a value like "N/A", the double negative returns zero — which may or may not be acceptable depending on your use case.

Use this fix when your text values are numeric strings from a system export and you want them included in the total.

Fix 2: Use ISNUMBER to Filter Out Non-Numeric Values

When your arrays contain genuine non-numeric text that should be excluded rather than converted, filter it out explicitly using ISNUMBER as a mask:

=SUMPRODUCT((ISNUMBER(A2:A10))*(ISNUMBER(B2:B10))*A2:A10*B2:B10)

ISNUMBER returns TRUE or FALSE for each cell. Multiplying by that boolean array effectively sets any row containing a non-numeric value to zero, while leaving valid numeric rows untouched. This is a deliberate exclusion rather than a coercion, so values like "TBD" or "N/A" are cleanly ignored.

Be aware that this approach means a row where even one of the arrays has text will contribute nothing to the total. If you only want to filter on one array, apply ISNUMBER only to that array's factor.

Fix 3: Multiply by 1 or Use VALUE() for Selective Conversion

Multiplying an array by 1 is a lighter-weight coercion technique — functionally similar to the double negative, but sometimes more readable in complex formulas:

=SUMPRODUCT((A2:A10*1), (B2:B10*1))

For surgical control, wrap individual ranges with VALUE(). This is clearest when only one column is known to contain text-formatted numbers:

=SUMPRODUCT(VALUE(A2:A10), B2:B10)

VALUE() will throw an error if the cell contains non-convertible text like "N/A", so this approach works best when every non-numeric cell is either empty or a valid numeric string. If there's any risk of truly non-numeric text, wrap VALUE() in IFERROR (covered below).

This same class of type-mismatch problem shows up in MATCH as well. If you're also dealing with lookup failures, see why MATCH returns #N/A when the lookup value is a number stored as text — the root cause overlaps significantly.

Fix 4: Use N() to Convert Logical or Mixed Values

The N() function converts its argument to a number: TRUE becomes 1, FALSE becomes 0, text becomes 0, and actual numbers pass through unchanged. It's particularly useful when one of your arrays is a boolean or logical expression rather than a stored range:

=SUMPRODUCT(N(A2:A10="Approved")*B2:B10)

In the formula above, N() converts the TRUE/FALSE comparison result into 1/0, which multiplies cleanly against the numeric B column. You won't normally use N() on a plain numeric range, but it's the right tool when an array is derived from a condition that might return mixed types.

Handling Errors Alongside Text: Wrapping With IFERROR

Text in an array sometimes causes errors rather than zero, especially when VALUE() or arithmetic coercion fails on a genuinely non-parseable string. IFERROR inside SUMPRODUCT lets you substitute a safe value for any cell that errors out:

=SUMPRODUCT(IFERROR(VALUE(A2:A10),0), IFERROR(VALUE(B2:B10),0))

This is the most defensive pattern. Any cell that can be converted to a number is converted; any cell that can't (true text labels, error values inherited from upstream formulas) is replaced with zero. The multiplication proceeds cleanly for every row.

One caution: wrapping with IFERROR can mask real problems — a formula error in your source data that should be investigated becomes a silent zero. Use this pattern when the data is inherently messy and you've consciously decided that non-numeric rows don't belong in the total.

The same tension between masking and surfacing errors is common across aggregation formulas. If you regularly work with COUNTIFS too, it's worth understanding why COUNTIFS returns zero when criteria ranges are different sizes — a separate but related silent-failure pattern.

Common Pitfalls to Avoid

Using IF inside SUMPRODUCT without array entry

In older Excel versions, placing IF inside SUMPRODUCT without Ctrl+Shift+Enter (or without proper array syntax) produces wrong results. In modern Excel (365/2019+) dynamic arrays handle this correctly, but if you're on an older version and your formula has IF inside SUMPRODUCT, verify it was entered as an array formula.

Applying the fix to only one array

It's easy to coerce the first array and forget the second. If both arrays can contain text, both need the same treatment. A mismatch in coercion between arrays still produces wrong totals.

Confusing empty strings with true blanks

A cell containing "" (an empty string formula result) is not the same as a genuinely empty cell. ISNUMBER returns FALSE for both, but they fail coercion differently. Use IF(A2="",0,VALUE(A2)) inside SUMPRODUCT if you need to distinguish them.

Assuming the green triangle means the fix worked

Excel's "number stored as text" warning triangle disappears when you dismiss it, not necessarily when the underlying data is fixed. Always re-run your ISNUMBER diagnostic after applying a fix to confirm the array is clean.

Forgetting that SUMPRODUCT doesn't handle errors like SUM does

SUM ignores error values; SUMPRODUCT propagates them. A single #DIV/0! or #VALUE! in your input range will cause the entire SUMPRODUCT to return an error. Wrap problematic ranges in IFERROR proactively if error values are possible.

For related XLOOKUP failures that stem from the same approximate-match and type-mismatch issues, this guide on XLOOKUP returning #N/A in approximate match mode covers the lookup side of the same problem class.

Wrapping Up

SUMPRODUCT's silent zero behavior when encountering text is one of the harder bugs to catch because the formula returns a plausible-looking number rather than an error. Here are the concrete steps to take right now:

1. Run the diagnostic: use =SUMPRODUCT(--(ISNUMBER(range))) on each input array and compare against COUNTA to find contaminated cells.
2. Choose the right coercion strategy: double negative or *1 for numeric strings; ISNUMBER masking for non-convertible text that should be excluded; IFERROR(VALUE(...),0) for the most defensive handling.
3. Apply the fix consistently to all arrays in the formula, not just the one you spotted the problem in.
4. Fix the source data if possible — use Text to Columns (Data tab, no delimiters) to bulk-convert text-formatted numbers to real numbers in the affected range.
5. Add a data validation rule to the input columns to reject non-numeric entries at the point of entry, so the problem doesn't creep back in.