Excel SUM Returns Zero or Misses Values: Convert Numbers Stored as Text

Confirm the problem is the stored type

To reproduce this case, import the sample with amount explicitly set to Text. CSV stores characters, so the same file could instead become numeric cells under a different import setting. This guide describes the worksheet state after a text import, not a claim that every CSV will open that way.

In an empty cell, enter ISNUMBER(B2). FALSE confirms that the referenced value is not numeric; it does not explain why. Check that your SUM range includes every intended amount as well. A range that stops early is a different problem and will not be fixed by converting cell types.

=ISNUMBER(B2)
Expected for imported text 12.50: FALSE

=SUM(B2:B5)
Expected when all four amounts are text: 0

=COUNT(B2:B5)
Expected when all four amounts are text: 0

Choose a conversion that fits the source

First decide which columns are measures. R1 through R4 are entry identifiers and should stay text. The amount column contains signed decimal values using a period, with no currency symbols. That narrow source contract makes a conversion straightforward.

For plain numbers matching your Excel locale, the error menu may offer Convert to Number. For a reproducible cleanup or a source with different separators, use a helper formula so the source and converted values remain visible together.

Convert in a helper column

Add the heading numeric_amount in C1. Enter the formula below in C2 and fill it through C5. It specifies a period decimal separator and comma grouping separator. The blank check preserves an empty input as an empty-looking result rather than deliberately turning it into zero.

The sample has no blanks, so all four outputs should be numeric. If your Excel uses semicolons between function arguments, use the alternate syntax shown below. Keep the quoted period and comma characters unchanged because they describe the source numbers.

=IF(B2="","",NUMBERVALUE(B2,".",","))

Semicolon argument-separator version:
=IF(B2="";"";NUMBERVALUE(B2;".";","))

Expected C2:C5, shown to two decimals:
12.50
7.25
-2.00
0.00

Reconcile count, sign, and total

Check COUNT(C2:C5)=4 and SUM(C2:C5)=17.75. Confirm that the adjustment remains negative and that the last value is a genuine numeric zero. Counting numeric cells helps distinguish a valid zero from an unconverted text value which happens not to change the total.

On a larger table, inspect conversion errors and compare a handful of source/output pairs before using the total. Do not wrap every failure in IFERROR with zero: that can conceal malformed amounts and make a partially converted table appear complete.

Investigate values that still fail

A displayed amount may include a currency prefix, an unusual space, a trailing note, or a separator from another locale. Compare the raw text with the source specification. Clean only the known extraneous characters in the amount column; a broad replacement across the table can damage descriptions and identifiers.

NUMBERVALUE interprets supported numeric syntax, including percent signs, rather than validating your business rules. If this column must contain plain amounts, separately flag percent-marked entries or other unexpected forms. A successful conversion alone does not establish that the input was acceptable.

Related help: Convert decimal commas with a known source locale

Keep a clear handoff after conversion

Use the helper column for downstream calculations once it passes the checks. If you need to replace the old column, keep a copy of the original worksheet and paste the verified results as values. Record the separator convention so the same process can be repeated on the next export.

Column Harbor reads CSV text, not Excel cell types or formulas. It cannot determine whether your existing workbook stored a visible amount as text, and it does not run SUM or NUMBERVALUE. The workbook checks above are necessary for this specific problem.