Why does SUMPRODUCT return #VALUE! with text in the range?
Formula (Excel and Google Sheets)
=SUMPRODUCT(B2:B7,C2:C7)Multiplying with * fails on text. Give the ranges as separate arguments, which treats text as 0: =SUMPRODUCT(B2:B7,C2:C7).
- Result on the example
- $170.90 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(B2:B7,C2:C7)Result170.9
B2:B76 rows × 1 col: 12, 5, 2, 30, …A range: 6 cells in one column.
C2:C76 rows × 1 col: $1.25, "TBD", $64.90, $0.45, …A range: 6 cells in one column.
SUMPRODUCT(B2:B7,C2:C7)170.9SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B76 rows × 1 col: 12, 5, 2, 30, … - array2
C2:C76 rows × 1 col: $1.25, "TBD", $64.90, $0.45, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(B2:B7,C2:C7). We ran it in Google Sheets mode on the same data and it returns $170.90.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(IFERROR(B2:B7*C2:C7,0)) | $170.90 | Multiplies and drops the rows that error. Google Sheets needs ARRAYFORMULA; Excel 2019 and earlier need Ctrl+Shift+Enter. |
| =SUMPRODUCT(B2:B7*C2:C7) | #VALUE!Different result | 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).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does SUMPRODUCT return #VALUE! with text in the range
E2, total quantity times price for all items, without an error from the "TBD" price.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.