Excel Not Summing Numbers? Causes and Fixes

How-to basics · Updated November 18, 2026

If Excel is not summing numbers, the most common cause is that some values are stored as text rather than as numbers. Check the cells’ contents and the SUM range first; then verify calculation settings, filters, and whether the formula is returning an error or an unexpected result.

Check Whether the Values Are Numbers or Text

Excel can display text that looks exactly like a number. This often happens when data is pasted from a website, imported from a CSV file, or entered with a leading apostrophe. A value such as 125 may appear normal in a cell but be stored as text. The SUM function generally ignores text in referenced cells, so a column can look complete while its total is too small or zero.

Look for clues before changing the data. Numbers stored as text are often left-aligned by default, while numbers are usually right-aligned, though cell alignment can be customized and is not proof by itself. A small green triangle in a cell’s corner may indicate that a number is stored as text. Select a suspicious cell and inspect the formula bar: a leading apostrophe may reveal text entry. You can also test a sample with a simple calculation or compare it with a known numeric cell.

For example, if B2:B5 contains numeric values 12, 8, and 15, but the apparent 5 in B5 is stored as text, the sum may be 35 rather than 40. Fixing the underlying type is usually better than trying to disguise the issue with number formatting. Formatting changes how a value looks; it does not reliably convert text into a number.

Convert Text Values into Numbers

Choose a conversion method that suits the source and the size of the list. Before changing imported data, consider keeping a copy of the original column so you can compare results or recover the source values if a conversion changes their interpretation.

  • Use the warning menu: If Excel shows a green triangle, select the affected cells, click the warning icon, and choose Convert to Number. This is convenient for a short list, but check the result when the cells contain unusual separators or formatting.
  • Use Text to Columns: Select the affected column, choose Data > Text to Columns, leave the appropriate delimiter options unchanged if you do not need to split the data, and choose Finish. This can convert text-formatted numeric entries in place. Check the preview and results, especially if the data uses a decimal or thousands separator that differs from your regional settings.
  • Multiply by one: Enter 1 in an empty cell, copy it, select the text-number cells, and use Paste Special > Multiply. This can coerce many text numbers to numeric values. Avoid it if the selection includes labels or other text you need to preserve.
  • Convert a single value in a helper column: With the text value in A2, enter =VALUE(A2) in B2. If A2 contains the text 125, the result is the number 125. Fill the formula down, check the results, then copy and paste values if you want to replace the source data.

Conversion can fail or produce unexpected results when the text contains currency symbols, spaces, nonbreaking spaces, or separators from another locale. For example, a comma can mean a thousands separator in one setting and a decimal separator in another. If imported values contain such characters, inspect a few raw entries and confirm how Excel interprets them before converting the entire column.

Confirm That SUM Includes the Right Cells

A correct total still depends on the range. Select the formula cell and examine the colored outline Excel displays around referenced cells. Check that the selected range includes the first and last intended values and does not stop one row early. A new row added below a fixed range may not be included automatically, particularly if the list is not an Excel table.

For example, if the numbers are in B2:B6, the formula =SUM(B2:B6) adds those five cells. If B6 contains the last value but the formula instead refers to B2:B5, the result will be short by B6’s amount. In a small test where B2:B6 contain 12, 8, 15, 5, and 10, the formula returns 50. This is a quick way to verify whether a suspected range includes all entries.

Also check for a formula that points to the wrong column, a different worksheet, or a range with gaps. If the data is on a sheet named Sales, a reference such as =SUM(Sales!B2:B20) sums that sheet’s B2:B20 range. Excel adds the exclamation mark between the sheet name and cell reference. A misspelled sheet reference generally produces an error rather than a plausible total, but a valid reference to the wrong range can be harder to spot.

If you regularly append rows, convert the list into a table using Insert > Table. A total formula can then refer to a table column, and the table expands as rows are added. This can reduce the chance of leaving new records outside a fixed cell range. Confirm that the table includes the intended header and data rows when you create it.

Check Calculation Settings and Formula Results

Sometimes the formula is correct but the displayed result has not updated. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and select Automatic. In Manual calculation mode, formulas may not recalculate whenever their inputs change. Press F9 to recalculate formulas; if the workbook remains in manual mode, switch it back to Automatic when appropriate.

Excel for Mac has calculation controls on the Formulas tab as well, though the ribbon layout can differ by version. Excel for the web generally recalculates supported formulas automatically, but a workbook’s calculation behavior can depend on how it was saved or edited in desktop Excel. If values seem stale, open the workbook in the desktop app and check its calculation setting.

Look at what appears in the cell, not just what you expect. If the cell displays the formula text instead of a result, it may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General or Number, then re-enter the formula; changing the format alone may not cause an existing text entry to become a formula. Toggle Show Formulas from Formulas > Show Formulas, or use Ctrl+` on Windows. On Mac, use the Show Formulas control on the Formulas tab if the keyboard shortcut differs on your keyboard.

Also check for an error such as #VALUE! or #REF!. An error can mean that an input or reference is invalid, not that Excel has silently failed to add the numbers. Resolve the underlying error or inspect the formula references. A result of zero can mean the range contains no numeric values, but it can also be a legitimate total; test the data type and range before assuming a calculation fault.

Understand Filters, Hidden Rows, and Subtotals

A filter can make a total look inconsistent because the rows you see are not necessarily the only rows included by SUM. The formula =SUM(B2:B20) adds numeric values in the referenced range even when some rows are hidden by a filter. This is useful when you want a grand total, but it surprises people who expect a filtered view to show only its visible records.

For a total that responds to filtering, use SUBTOTAL. With amounts in B2:B20, =SUBTOTAL(9,B2:B20) returns the sum while excluding rows filtered out of the list. The function number 9 specifies SUM. If you also want to exclude rows hidden manually, use =SUBTOTAL(109,B2:B20). These options can help when reviewing a filtered report, but they are not replacements for SUM in every situation: decide whether hidden rows should count.

Another source of confusion is a subtotal row included in the same range as the underlying detail. Adding both the detail and its existing subtotal counts those values twice. If a report has section totals, make sure the grand-total formula refers to detail rows only, or use a structure designed to avoid including subtotal rows in the aggregate.

Check Number Formats, Units, and Imported Characters

Formatting can make valid numbers appear wrong without preventing the sum. A cell formatted as Currency may show a currency symbol, while a cell formatted with zero decimal places may hide cents. The stored value can still include decimals even when they are not displayed. Select the cell and look at the formula bar or increase decimal places to see more of the value.

Conversely, changing a text cell to Number format does not necessarily convert it. If an entry remains text, the cell may continue to display left-aligned characters and SUM may still ignore it. Use a conversion method, then verify the stored value rather than relying on the appearance of a currency symbol or decimal point.

Check that the values use consistent units. A total that adds dollars to cents, hours to minutes, or quantities from different measurement systems can be mathematically valid but misleading. Excel cannot infer that 250 represents cents while 2.50 represents dollars. Standardize units before summing, and label the result so a reader knows what it represents.

Imported data may contain leading or trailing spaces or special characters that ordinary trimming does not remove. If a conversion fails, compare the suspicious entry with a value typed directly into Excel and investigate the source. For a small dataset, retyping a few affected values may be practical; for a recurring import, correcting the export or using a repeatable data-cleaning process is more reliable.

A Practical Troubleshooting Order

Work from simple checks to less obvious causes. This avoids changing a whole dataset when the actual problem is just a formula range that ends too soon.

  1. Select the result cell and confirm that it contains a SUM formula, not a typed value or a formula stored as text.
  2. Inspect the formula’s references and verify that the first and last intended cells are included.
  3. Check a few source cells for text numbers, apostrophes, warning triangles, or inconsistent imported characters.
  4. Convert a sample value and see whether the total changes as expected before applying the same fix to the full column.
  5. Check Formulas > Calculation Options and recalculate if the displayed answer appears stale.
  6. Decide whether filtered or manually hidden rows should count, and use SUM or SUBTOTAL accordingly.
  7. Review formatting, units, and any subtotal rows that might be omitted or counted twice.

For a fast diagnostic, create a temporary test in an unused cell using the same range as the original total. If that produces the same result, the issue is likely in the source values, range, or assumptions about hidden rows rather than in the original result cell’s formatting. Remove the test formula after checking so it does not confuse later users.

Frequently Asked Questions

Why does Excel show zero when I use SUM? The range may contain values stored as text, be empty, or refer to the wrong cells. Select a few source cells, check their contents and formula references, and try converting a text-formatted number to a number. A zero can also be the correct result if positive and negative values cancel each other.

Why does SUM ignore numbers stored as text? Text is not the same data type as a numeric value, even if it looks like one on screen. SUM generally ignores text in referenced cells. Convert the entries to numbers, then confirm that the total changes and that the conversion has not misread separators or units.

Does SUM include filtered-out rows? Yes. SUM adds numeric values in its specified range, including rows hidden by a filter. Use =SUBTOTAL(9,B2:B20) when B2:B20 contains the values and you want a sum that excludes filtered-out rows. Use function number 109 if manually hidden rows should also be excluded.

Why does Excel not update my total after I edit a value? Check Formulas > Calculation Options and select Automatic. If calculation is set to Manual, press F9 to recalculate. Also check whether the result cell refers to the edited cell and whether the edited entry was actually recognized as a number.

Will changing a cell to Number format fix a text number? Not necessarily. Number format changes how numeric values are displayed; it does not reliably change text into numeric data. Convert the value using Excel’s warning menu, Text to Columns, or a suitable formula, and then verify the result.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.