Excel SUM Formula Not Working? How to Fix It

If Excel’s SUM formula is not working, check that the formula begins with an equals sign, its range includes the intended cells, and the values are real numbers rather than text. Then check for errors in the range, a formula displayed as text, or calculation set to Manual; these are the most common causes of a wrong or unchanged result.

How SUM is supposed to work

SUM adds numbers in cells or ranges. In Microsoft Excel for Microsoft 365 on Windows, enter a formula in the cell where you want the total, then press Enter. For example, with amounts in B2 through B5, the formula below adds those four cells. If they contain 12, 8, 15, and 5, the result is 40.

With amounts in B2:B5, enter: =SUM(B2:B5)

The colon means “through,” so B2:B5 includes every cell from B2 to B5. A formula can also add separate ranges or individual cell references. With sales in B2:B5 and a separate adjustment in D2, this formula adds both; if the sales total 40 and D2 is 3, the result is 43.

With sales in B2:B5 and an adjustment in D2, enter: =SUM(B2:B5,D2)

SUM ignores text in referenced cells, including a label such as “Total” if that label is accidentally inside the range. It does not automatically convert every text-looking number into a numeric value. That distinction explains many cases where a formula returns a plausible but incomplete total.

Check the range and formula syntax

A correct SUM formula can still give the wrong total if its references are wrong. Click the formula cell and inspect the colored outlines Excel shows around referenced cells. Confirm that the first and last rows are correct, that no intended column was left out, and that the formula points to the current sheet and workbook.

Look for a skipped row: If new data was added below the range, the total may still stop at the old last row.
Check the column: A formula intended to total sales may point to quantities or dates instead.
Watch for a misplaced label: If a heading or subtotal is inside the selected range, it may not be added as expected. Including a subtotal that already includes other rows can also double-count amounts.
Check copied formulas: When a formula is copied down or across, relative references may shift. Click each result and verify the references rather than assuming they stayed fixed.

For example, suppose the values are in B2:B6, but the formula totals B2:B5. The omitted value in B6 is not a SUM error; it is outside the requested range. Expand the range to include the missing row. When you use AutoSum from Home > AutoSum, Excel suggests a nearby range, but you should still inspect the highlighted selection before accepting it.

Numbers stored as text

Cells can look numeric while containing text. This commonly happens after copying data from a website, importing a CSV file, or receiving values with leading apostrophes or spaces. A green triangle in a cell may indicate that a number is stored as text, though its absence does not prove the value is numeric. Since SUM ignores text in a referenced range, the total can be lower than expected, or even zero.

First select a suspicious cell and inspect the formula bar. If the value is left-aligned while nearby numbers are right-aligned, or Excel flags it as text, investigate further; alignment alone is not conclusive because formatting can change alignment. If Excel offers an error-checking button, choose Convert to Number. For a group of cells, select them and use the warning menu if available. Another option is Data > Text to Columns, then complete the wizard with the appropriate settings; this can convert text-formatted numeric entries to numbers.

After conversion, check whether the total changes and spot-check a few individual cells. Do not blindly convert every text value: identifiers such as product codes, postal codes, and account numbers may intentionally be text, even when they contain only digits. A leading zero in an identifier can be significant.

Formula shown instead of a result

If you see the characters =SUM(B2:B5) in the cell rather than a number, Excel may be treating the entry as text. Select the cell and look at Home > Number Format. If it is set to Text, change it to General, then edit the formula and press Enter. Changing the format alone may not make an existing text entry calculate; re-entering it is important.

Also check for an apostrophe or space before the equals sign. A leading apostrophe tells Excel to store an entry as text. Delete it, along with any unintended leading spaces, and enter the formula again. If formulas are visible across many cells, you may have toggled Show Formulas rather than changed the individual cells. On Windows, press Ctrl+` (the grave accent key, usually near Esc) to toggle that display, or go to Formulas > Formula Auditing > Show Formulas. The Mac keyboard shortcut can vary with keyboard layout; use the Show Formulas command on the Formulas tab if needed.

Calculation settings and stale results

Excel normally recalculates formulas automatically when referenced values change. If a SUM result remains unchanged after you edit its inputs, calculation may be set to Manual. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and select Automatic. The option affects the workbook calculation behavior in Excel, so check it when multiple formulas seem stale, not only one total.

After selecting Automatic, change a source value or request a recalculation and see whether the result updates. F9 recalculates open workbooks; Shift+F9 recalculates the active worksheet. If you use a Mac, keyboard behavior can depend on the keyboard and function-key settings; the Calculation Options command is also available from the Formulas tab. A full recalculation can be useful in a complex workbook, but it is usually unnecessary for a straightforward SUM formula.

Manual calculation is sometimes chosen deliberately in large workbooks to avoid slow recalculation during editing. If you switch it on for a particular task, remember to recalculate before relying on results. A total that has not refreshed is not necessarily a broken formula; it may simply be an old calculated value.

Errors inside the range

If any cell included in a SUM range contains an error such as #N/A or #VALUE!, the SUM result can show that error rather than a numeric total. Find the error in the referenced cells and correct its underlying cause. For a large range, use Find or inspect the cells highlighted by the formula references. Fixing the source error is usually better than hiding it, because the error may signal missing or invalid data.

Blank cells and text labels generally do not cause an error in a referenced range, but they can expose a data-entry problem if a numeric amount was expected there. A cell containing a formula that returns an empty string may look blank while still being the result of a formula. If totals differ from expectations, inspect the source cells rather than judging only by their appearance.

Subtotal, filtered rows, and visible-only totals

SUM adds all numeric cells in its stated range, including rows hidden by filtering or manually hidden. This is a frequent source of confusion: filtering a list changes which rows are visible, but a regular SUM does not mean “sum only what I can see.” If you need a total that responds to filtered rows, use SUBTOTAL instead. With amounts in B2:B100, the formula below uses function number 9 to sum visible rows while excluding rows hidden by a filter.

With amounts in B2:B100 and a filtered list, enter: =SUBTOTAL(9,B2:B100)

SUBTOTAL is an alternative for a specific need, not a repair for every incorrect SUM. In particular, understand what is hidden and whether manually hidden rows should be included in your reporting. Check that your range is still correct, and compare the result with a small hand-calculated sample before using it in a report.

Locale, separators, and other practical checks

Excel’s formula argument separator depends on regional settings. The examples here use commas, as in =SUM(B2:B5,D2). Some installations expect semicolons between function arguments. If Excel rejects a formula with a syntax message, check the separator used by your installation or let Formula AutoComplete guide the entry. The colon in a range such as B2:B5 is not an argument separator.

Also confirm you are entering the formula in a worksheet cell, not in the Name Box or a text editor, and that you press Enter to commit it. If the formula is valid but the result is unexpected, compare the source values with the intended units and check number formats. Formatting can make a value appear rounded or display as a date, but it does not ordinarily change the underlying number used by SUM.

A dependable troubleshooting order

Work through the checks in a consistent order so you do not change good data unnecessarily:

Click the total and confirm that the formula begins with = and uses the intended range.
Inspect the cells in that range for omitted rows, unexpected labels, duplicate subtotals, or errors.
Check whether numeric-looking entries are stored as text, and convert only genuine numeric data.
If the formula itself appears on screen, check the cell format, leading characters, and Show Formulas mode.
If the answer is stale, check Formulas > Calculation Options > Automatic and recalculate.
If the list is filtered and the goal is a visible-row total, decide whether SUBTOTAL is a better fit than SUM.

When a total matters, verify it independently: add a few known values by hand, check a smaller section of the range, or compare the result with a pivot or another appropriate summary. This is especially useful after importing data or changing formula ranges. It helps distinguish a formula problem from a problem in the underlying records.

Frequently Asked Questions

Why does SUM return zero?
If the referenced cells contain text instead of numbers, SUM may ignore them and return zero. Check whether the values were imported as text, whether the range is correct, and whether an error or formula display issue is obscuring the result.

Why is SUM not adding one cell?
That cell may be outside the formula’s range, stored as text, or blank. Click the formula to inspect its references, then inspect the cell’s contents and data type. Formatting a text value as a number alone may not convert it.

Why does Excel show the SUM formula instead of the answer?
The cell may be formatted as Text, the entry may have a leading apostrophe or space, or Show Formulas may be enabled. Set the cell to General and re-enter the formula, remove unintended leading characters, or turn off Show Formulas.

Does SUM ignore filtered-out rows?
No. SUM includes numeric cells in its range even when rows are hidden by a filter. Use SUBTOTAL when you specifically want a total that responds to filtered rows, and confirm that its hidden-row behavior suits your purpose.

Why does my SUM result not update when I change a value?
Calculation may be set to Manual. Select Formulas > Calculation Options > Automatic, then recalculate. Also confirm that the edited cell is actually included in the formula’s range.

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.