Excel SUM Not Working? Fix Common Formula Problems

If Excel SUM does not work, first check that the formula starts with an equals sign, covers the intended cells, and is being recalculated; then check whether the values are stored as numbers rather than text. In Microsoft 365 for Windows, a basic total looks like =SUM(B2:B6)—with values 12, 8, 15, 5, and 10 in B2:B6, it returns 50.

Check the formula and the range first

A SUM formula can be syntactically valid but still total the wrong cells. Click the cell containing the result and inspect the formula bar. Confirm that the formula begins with =, names the expected worksheet and range, and has not accidentally omitted the last row. For example, a formula ending at B5 will not include a value in B6.

To check a simple total, with the five numbers 12, 8, 15, 5, and 10 in B2:B6, enter =SUM(B2:B6) in another cell. The result is 50. If the data is in a different column or starts on another row, adjust the range accordingly. You can also select the cells and look at the status bar at the bottom of Excel; when numeric cells are selected, it can display their sum. That is a quick comparison, not a replacement for checking the formula’s range.

Make sure the formula refers to the sheet you intend. A reference such as =SUM(Sheet2!B2:B6) totals the range on Sheet2, not the current sheet.
Look for a row added below the formula’s range. A normal range does not necessarily expand just because new data is typed nearby.
Check for an accidental space or character before the equals sign. If Excel treats the entry as text, it will not calculate it as a formula.

Numbers stored as text are a frequent cause

SUM adds numeric values but ignores text in a referenced range. A cell can look like 12 and still contain text, perhaps because it was imported from a website, CSV file, or another application. A leading apostrophe, spaces, or a mismatch between the decimal and thousands separators can also leave numeric-looking data stored as text. The number format alone does not determine the underlying value: changing a text cell’s format to Number may change its appearance without converting its contents.

Check a suspicious cell for a green warning triangle or a left-aligned value, although alignment is only a clue because formatting can override it. Select the cell and inspect the formula bar. If Excel offers a warning menu, choose Convert to Number. For a whole imported column, select it and use Data > Text to Columns, then complete the wizard with Finish. Check a few converted cells and the total afterward, especially if the column contains dates, IDs, or values with unusual punctuation.

For a single text value that Excel can interpret as a number, with the text value 12 in B2, enter =VALUE(B2) in C2; it returns the numeric value 12. You can then use the converted values in a total. VALUE follows the current regional conventions for recognizing numbers, so a decimal comma or date-like string may not parse as intended on every computer. For an imported column, conversion tools and a visual check are usually safer than assuming every entry has the same format.

Check calculation mode and refresh the result

If you change the source numbers but the displayed total stays the same, Excel may be set to manual calculation. In Microsoft 365 for Windows, open Formulas > Calculation Options and select Automatic. Automatic calculation normally updates formulas when their inputs change; manual mode waits for a recalculation command. The setting can be particularly noticeable in a large workbook or one received from someone else.

After switching to Automatic, edit a source cell or recalculate to see whether the result updates. Press F9 to calculate formulas in open workbooks. On some laptops, you may need to use the Fn key with a function key. Recalculation shortcuts and function-key behavior can vary by keyboard and Excel platform; on Mac, use the Excel menu’s calculation commands or the shortcut listed for your installed version. Excel for the web generally recalculates automatically and has fewer calculation controls than desktop Excel.

Manual mode does not usually erase a result; it can leave a previously calculated value on screen until Excel recalculates. If a workbook’s formulas appear stale, check calculation mode before replacing formulas or re-entering data. Recalculation can take time in a workbook with many complex formulas, so give Excel a moment to finish.

When Excel displays the formula instead of the answer

If a cell shows =SUM(B2:B6) literally, Excel may be displaying formulas across the sheet, or that particular entry may be stored as text. In Windows desktop Excel, select Formulas > Show Formulas to toggle formula display. The Windows shortcut is Ctrl+`; the key is usually the grave-accent key, often beside 1. On Mac, Control+` is commonly used, though keyboard layouts can differ. When Show Formulas is on, column widths may expand and formulas appear in cells instead of their calculated results.

If only one cell shows the formula, select it and check its number format and the characters before the equals sign. Change a Text-formatted cell to General, then re-enter the formula—press F2 and Enter, or type it again. Merely changing the format may not make Excel reinterpret an existing text entry. Also check for a leading apostrophe or space before the equals sign, both of which can make the entry text rather than a working formula.

Understand blanks, text, and error values in the range

SUM ignores blank cells and ordinary text in a referenced range, so a total may be smaller than expected without showing an error. A label such as “Pending” in the amount column will not contribute a number. That behavior is useful when a range includes headings, but it can hide data-quality problems if numeric-looking amounts were imported as text. Compare the number of populated rows with the number of numeric entries before deciding the formula is faulty.

An error value is different from ordinary text. If a referenced cell contains an error such as #VALUE! or #REF!, the SUM result can show that error rather than a total. Find the source error and correct its cause; hiding it can conceal a genuine problem. For instance, a broken cell reference should be repaired at its source rather than excluded just to make the total display.

Formatting can also make a correct value look wrong. A cell formatted with zero decimal places may display 12 even though its stored value is 12.49, and a currency format can round the display while keeping extra precision underneath. Select the result and increase the displayed decimal places from the Home tab if you need to inspect precision. Formatting changes what you see, not the underlying arithmetic.

Filtered lists need a different total sometimes

SUM includes numeric values in rows hidden by a filter, as well as values in manually hidden rows. That is often correct for a grand total, but surprising when a reader expects the result to match only the visible records. For a filtered list, use SUBTOTAL with function number 9. With values 12, 8, 15, 5, and 10 in B2:B6, and a filter leaving 12, 15, and 10 visible, =SUBTOTAL(9,B2:B6) returns 37. SUBTOTAL ignores rows removed by the filter.

Function number 109 tells SUBTOTAL to also exclude manually hidden rows. In a filtered list, both 9 and 109 exclude filtered-out records; the distinction is whether manually hidden rows are included. If you need a total of every row regardless of visibility, SUM remains appropriate. Choose based on what the report is meant to communicate, not just on which formula produces a smaller number.

Prevent repeat problems with tables and consistent data

For recurring lists, format the data as an Excel table using Insert > Table, or Home > Format as Table. Tables expand as rows are added and make it easier to keep formulas and ranges aligned. If a table has a Total Row, its calculation options can use SUBTOTAL-style behavior so the displayed total responds to filtering. Confirm the chosen calculation, since the Total Row can provide options other than Sum.

Keep each column consistent: amounts should contain numbers, not a mixture of numbers, labels, and symbols typed into the same cells. Put currency symbols in the cell format rather than entering them as text. When importing data, inspect separators, decimal marks, and a sample of converted values before relying on the total. A useful check is to compare a small hand-calculated sample against the formula, then review unusual blanks and error cells.

Frequently Asked Questions

Why does SUM return 0 when the cells contain numbers?
Those entries may be numbers stored as text, which SUM ignores in a referenced range. Check a sample cell and use Convert to Number or Text to Columns to convert the values. Also confirm that the formula’s range actually covers those cells.

Why does my SUM formula show in the cell?
Show Formulas may be enabled for the worksheet, or the individual cell may be formatted as Text. Turn off Formulas > Show Formulas if every formula is visible, or change the affected cell to General and re-enter the formula.

Why does the total not change when I edit a value?
Check Formulas > Calculation Options and select Automatic. If calculation is already automatic, make sure the edited cell is inside the range and that the displayed formula points to the correct sheet and cells.

Does SUM ignore hidden or filtered rows?
No. SUM includes numeric values in hidden rows. Use SUBTOTAL with function number 9 to total filtered-visible rows, or 109 if manually hidden rows should be excluded too.

Why is my total slightly different from the displayed amounts?
The cells may contain more decimal places than their formatting displays, so SUM uses precision that is not visible in the rounded display. Increase decimal places to inspect the stored values, and decide whether the source amounts should be rounded before totaling.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.