# Why does SUMPRODUCT return #VALUE! with text in the range?

Multiplying with * fails on text. Give the ranges as separate arguments, which treats text as 0: =SUMPRODUCT(B2:B7,C2:C7).

**Excel:** `=SUMPRODUCT(B2:B7,C2:C7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Item | Qty | Price |  | Order total |
| Pens | 12 | 1.25 |  |  |
| Paper | 5 | TBD |  |  |
| Toner | 2 | 64.9 |  |  |
| Folders | 30 | 0.45 |  |  |
| Labels |  | 8.99 |  |  |
| Tape | 6 | 2.1 |  |  |

Formula in E2: `=SUMPRODUCT(B2:B7,C2:C7)`

Result: $170.90

## How it works

Written as `=SUMPRODUCT(B2:B7*C2:C7)`, the multiplication happens first, and 5 times "TBD" is #VALUE!, which spoils the whole result. Given as separate arguments, SUMPRODUCT multiplies the ranges itself and treats text as 0, so the total comes out at 170.90.

## Other ways

- `=SUM(IFERROR(B2:B7*C2:C7,0))`: Multiplies and drops the rows that error. Google Sheets needs ARRAYFORMULA; Excel 2019 and earlier need Ctrl+Shift+Enter.
- `=SUMPRODUCT(B2:B7*C2:C7)`: Shown for contrast: the * form that fails. (Returns #VALUE! because of the text price "TBD".)

## Common mistakes

- The ranges must be the same size, or SUMPRODUCT returns #VALUE! however you write it.
- Treating text as 0 means Paper adds nothing. Check for missing prices with `=COUNT(C2:C7)`.
- Conditions still need the * form, with the values as a separate argument: `=SUMPRODUCT((A2:A7="Pens")*B2:B7,C2:C7)`.

## Questions

**When do I need the * form?**

For conditions, which are TRUE/FALSE arrays that must be multiplied: `=SUMPRODUCT((B2:B7>5)*C2:C7)`.

**Why does a blank quantity not error?**

An empty cell counts as 0 in both forms. Only text causes #VALUE!.

**Why does SUMPRODUCT return 0 instead?**

The numbers are probably text. SUMPRODUCT with commas treats them as 0; convert them with `--` or VALUE.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sumproduct-value-error