Excel SUM skips pasted numbers—even after Paste Values

A partially correct expense total can be harder to spot than a total of zero. Some amounts were typed into the workbook; others arrived from a website or an export. Every row looks populated, and the total looks plausible. Yet one group of rows may never have entered the calculation.

The useful starting point is a single omitted amount. Establish whether it belongs to the SUM range, then inspect its stored type. If it is text, a separate conversion column lets you repair the report while retaining enough evidence to check what changed.

Find one amount SUM is skipping

Click the total and inspect its range. If the final amount is in B4 but the formula stops at B3, conversion will not fix the omission. For this example, assume the formula correctly reads =SUM(B2:B4), with the following stored values.

The amounts should total 4500, but this SUM returns 2300. It ignores text within the referenced range. That rule concerns values in cells; do not assume every formula or every way of supplying an argument handles numeric text identically. Microsoft’s SUM documentation

In an empty cell, enter =ISNUMBER(B3). TRUE identifies a number. FALSE means it is not a number, which alone does not prove it is text: blanks and errors also need consideration. Use =ISTEXT(B3) to check that distinction. For the example above, the results are FALSE and TRUE respectively. Alignment is only a clue because users can change it. Microsoft’s IS functions reference

Also check =COUNT(B2:B4). It returns 2 for this example, revealing that only two numeric cells were available for three intended amounts. A genuine zero counts as a number, so this check can expose omissions that would not change the sum. Microsoft’s COUNT documentation

Convert beside the original column

If Excel displays a number-stored-as-text warning, select the affected amount cells and choose Convert to Number from the warning menu. That is convenient for a few straightforward entries. An absent warning is not proof that the cells contain numbers. A helper column is useful when the warning is unavailable or you need to compare the results with the original. Microsoft’s conversion instructions

Label B “Original amount” and an unused C column “Converted amount.” Enter =VALUE(B2) in C2 and fill through the populated amount rows. If there are missing entries within the range, use =IF(B2="","",VALUE(B2)) to retain blank results for them. In another unused column, =ISNUMBER(C2) can check each result. Keep B intact while examining failures.

VALUE converts text in a format Excel recognizes. If it cannot interpret the entry, it returns #VALUE!. Treat that as an unresolved amount rather than a nuisance to hide. The next sections address two common reasons a seemingly numeric entry still fails. Microsoft’s VALUE reference

Text extraction creates another trap. A formula using LEFT can extract the digits at the start of a quantity-and-description entry, but its job is to return characters. Pasting that result as a value removes the formula, not the fact that its result is text. Convert the extracted quantity with VALUE when you need a numeric result. Microsoft’s LEFT reference

Check the extraction itself, too. A formula taking the first two characters can truncate a three-digit quantity. Successful conversion would then produce a perfectly valid number with the wrong value. Compare longer entries, decimals and negative amounts against their originals before accepting the column.

Choose columns by their purpose. A product identifier such as 0142 is not an amount to add. Converting it to 142 changes how the item is identified. Limit the repair to amounts or quantities; do not select the entire sheet just because several columns contain digits.

Read the separators before removing them

An imported amount written as 1.234,50 may mean 1234.50. Deleting its comma changes the meaning of the fractional part. A source using 1,234.50 follows a different convention. Knowing that both entries are text does not tell you which conversion rule to apply.

Once you have established that the source uses a decimal comma and grouping dots, use =NUMBERVALUE(B2,",","."). The second argument specifies the decimal separator; the third specifies the grouping separator. Under this rule, text 1.234,50 becomes the numeric value 1234.5. Displaying two decimal places afterward is a formatting choice. Microsoft’s NUMBERVALUE reference

Do not infer a whole column’s convention from a single row if several suppliers’ exports were combined. Separate the sources first. An ambiguous entry such as 1,234 needs context from the original document or its export instructions. A conversion can return a number without returning the intended amount.

Preserve missing amounts, too. NUMBERVALUE turns an empty string into zero. If blank means “not supplied,” use a guard such as =IF(B2="","",NUMBERVALUE(B2,",",".")). This preserves a blank result for an empty cell or empty string. A cell containing spaces is a different case and needs inspection.

If Excel rejects the formula before calculating it, check its argument delimiters. Some settings require semicolons: =NUMBERVALUE(B2;",";"."). The quoted comma and dot still describe the source amount, so leave those intact. Microsoft’s formula delimiter guidance

Currency and percentage symbols also carry meaning. Removing labels from euro and won amounts does not make them suitable for one total. Keep the source’s units and signs in view while deciding what belongs in the report.

Handle spaces that TRIM leaves behind

Web content can include a nonbreaking space, or NBSP, that resembles an ordinary space. TRIM handles ordinary ASCII space characters but does not remove NBSP by itself. Microsoft identifies NBSP with character code 160. Microsoft’s TRIM documentation

For an amount with NBSP around its digits, try =VALUE(TRIM(SUBSTITUTE(B2,UNICHAR(160)," "))) in the helper column. This replaces that character with an ordinary space, trims the result and converts it. It assumes the cleaned amount uses a format VALUE can interpret in your Excel environment.

UNICHAR(160) identifies the character; SUBSTITUTE replaces that particular character. This is not a universal cleaner for invisible characters or currency text. The relevant building blocks are documented separately in UNICHAR and SUBSTITUTE.

If a row still fails, inspect it individually for a unit suffix, internal spaces or malformed punctuation. A space inside digits might group thousands, but it might also separate two fields that were accidentally combined. Deleting every space before understanding the source can merge two amounts into one larger number.

Reconcile the rows, then keep the results

For the three-row example, the converted column should give =COUNT(C2:C4) as 3 and =SUM(C2:C4) as 4500. The increase from 2300 is explained by the previously omitted 2200. That explanation matters: a larger total alone does not establish that the repair is correct.

In a real report, compare the numeric count with the number of rows that should contain amounts, excluding headings and identifiers. Check signs, decimal positions and units against the originals. Distinguish missing entries from genuine zero amounts so that an incomplete record does not silently become a zero-value transaction.

A blanket IFERROR(...,0) would hide unresolved conversions by treating unreadable amounts as zero. Leave those rows identifiable until the source can be clarified. An apparently clean total is not useful if nobody can tell which transactions it excludes.

After reconciliation, have the report reference the converted column, or copy its results into a separate final column with Paste Values. This time you are preserving numbers that have already been converted. If you intend to remove the helper formulas, retain and compare their pasted numeric results first.

For the next batch, extend both the conversion formulas and the total’s range through the new rows. Keeping original amounts, converted amounts and checks side by side makes a recurring omission much easier to locate than starting again from a suspicious grand total.