Why is my SUM formula returning 0 or too small a total in Excel

Formula (Excel and Google Sheets)

=SUMPRODUCT(--B2:B7)

SUM skips numbers stored as text, so convert them while adding with =SUMPRODUCT(--B2:B7) and then fix the column for good.

Result on the example: $6,199.40 in E1
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

How it works

Imported or pasted numbers often arrive as text: they sit on the left of the cell and may show a green triangle. SUM ignores them, so here it adds only 899 and 450. The double minus converts each value to a real number, text or not, and SUMPRODUCT adds the results.

=SUMPRODUCT(--B2:B7)

Result6199.4

B2:B76 rows × 1 col: "1250", "385.40", 899, "2,140", …
A range: 6 cells in one column.
-B2:B7{6 rows × 1 col} -1250, -385.4, -899, -2140, …
Makes the value negative.
--B2:B7{6 rows × 1 col} 1250, 385.4, 899, 2140, …
Makes the value negative.
SUMPRODUCT(--B2:B7)6199.4
SUMPRODUCT: Multiplies arrays row by row and adds the results.
- array1--B2:B7{6 rows × 1 col} 1250, 385.4, 899, 2140, …

In Google Sheets

The same formula works in Google Sheets: =SUMPRODUCT(--B2:B7). We ran it in Google Sheets mode on the same data and it returns $6,199.40.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUM(VALUE(B2:B7))Excel only	$6,199.40	Excel 365 calculates the array directly; older versions need Ctrl+Shift+Enter and Google Sheets needs ARRAYFORMULA.
=SUM(B2:B7)	$1,349.00Different result	What a plain SUM sees. Adds only the two real numbers: 1,349.

Common mistakes

A total of exactly 0 usually means every value is text; a total that is too small means some are. Check with =ISTEXT(B2).
Converting inside the formula hides the problem. Fix the column with Data > Text to Columns > Finish, or select it and use the warning icon's Convert to Number.
Text with letters ("n/a") can't be converted and gives #VALUE!. Clear or replace those cells first.

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.