How to Fix a SUM Error in Excel

To fix a SUM error in Excel, first check that the formula points to the right cells, then make sure the values are numbers and calculation is set to Automatic. For example, with amounts in A2:A6, a formula that totals them is =SUM(A2:A6). If the result is wrong or Excel displays an error, use the checks below to find the specific cause rather than replacing the formula blindly.

What You’ll Need

The Excel workbook and the cells you expect to add.
A rough idea of the correct total, such as a hand calculation or a total from a trusted source. This makes it easier to tell whether a fix worked.
If the workbook is shared or important, permission to change the source data or calculation settings. Consider saving a separate copy before making broad changes.

Step-by-Step

Identify what “error” means in your sheet. Is the result too small, too large, or zero? Does the cell show an error such as #VALUE! or #REF!? Or does the displayed total stay unchanged after you edit a value? These symptoms point to different causes. Select the total cell and look at the formula bar to inspect the actual formula, not just the displayed result.
Check the formula and its range. Confirm that the formula begins with an equals sign and uses the intended starting and ending cells. A missing row at the bottom of a range is a common reason for a low total. With the amounts to add in A2:A6, the complete formula is =SUM(A2:A6); if the amounts actually continue through A7, use =SUM(A2:A7) instead. Check that the formula is in the expected total cell and that you have not included a heading or an unrelated column by mistake.
Use AutoSum to create a fresh formula when needed. Select the blank cell directly below a column of numbers, then choose Home > AutoSum > Sum, or press Alt+= on Windows. Excel outlines the range it proposes. Check that outline before pressing Enter: extend or adjust the selection if it misses rows or includes cells that should not be counted. On Mac, use Home > AutoSum > Sum; the ribbon command is a reliable alternative if your keyboard layout does not provide the same shortcut. AutoSum is a shortcut for creating a formula, not a guarantee that Excel selected the correct range.
Check for numbers stored as text. A value can look like a number but be stored as text after being pasted from a website, imported from a CSV, or entered with an apostrophe. SUM ignores text in a referenced range, so the total can be lower than expected without showing an error. For example, with numeric 12 in A2, numeric 8 in A3, text-formatted 15 in A4, and numeric 5 in A5, =SUM(A2:A5) returns 25, not 40. Look for a green warning marker, left-aligned values, or a “Number stored as text” warning. Select the affected cells, open the warning menu, and choose Convert to Number when that option is offered.
Convert imported text values if Excel offers no warning. Select only the affected cells, choose Data > Text to Columns, and proceed through the wizard using the default options, then select Finish. Check the results before applying this to a mixed range: imported dates, codes with leading zeros, and values using a different decimal or thousands separator may need special handling. Another option is a helper column. With the text value in A4, enter =VALUE(A4) in B4; if A4 contains text representing 15, B4 returns the number 15. Fill the helper formula down, verify the converted values, and use the helper column in the total if appropriate.
Look for errors inside the cells being added. SUM can return an error if a referenced cell itself contains an error, such as #N/A, #VALUE!, or #REF!. Inspect the cells in the range and trace the first error back to the formula or source value that caused it. Fix that underlying issue where possible. Avoid wrapping the entire total in an error-hiding formula as a first step: hiding an error can make a workbook appear correct while leaving missing or invalid data unresolved.
Check whether the calculation range should include filtered or hidden rows. SUM includes values in rows hidden by a filter, which may make a total look too large when you expect it to show only visible records. If you want a total that responds to filtering, use SUBTOTAL. With amounts in A2:A100, =SUBTOTAL(109,A2:A100) adds visible values and excludes rows hidden by a filter or manually hidden; the result depends on which rows are visible. Use ordinary SUM when hidden rows should still count. Make this choice deliberately, since the two formulas answer different questions.
Set calculation to Automatic if results are stale. On Windows, open Formulas > Calculation Options > Automatic. Then recalculate the workbook by pressing F9. If you want to recalculate only the active worksheet, use Shift+F9. On Mac, look for Formulas > Calculation Options > Automatic; depending on the ribbon layout, the calculation setting may also be under Excel > Preferences > Calculation. If the workbook is in Manual mode, formulas may not update after you change source values.
Retest the total and save the corrected workbook. Change a source value temporarily to a different number and confirm that the total responds as expected, then undo the test or restore the original value. Compare the total with your independent check and confirm the formula range includes all intended rows. Save the file after confirming that other dependent totals still work.

Common Mistakes

One frequent mistake is assuming that every value that looks numeric is actually a number. Text-formatted numbers are especially common in imported reports, and SUM generally skips them without warning. Another is correcting the formula by expanding its range without checking what the extra cells contain; this can add subtotals, old records, or a second copy of the same data.

It is also easy to confuse SUM with a visible-rows-only total. A normal SUM counts hidden and filtered-out rows. If a report is filtered, decide whether the requested result should include every record or only the visible ones before changing the formula. Similarly, do not replace a source error with zero just to make the total display. That may conceal a broken lookup or missing input that needs attention.

Finally, do not overlook calculation mode. Pressing F9 can refresh formulas in a workbook set to manual calculation, but it does not fix an incorrect range or convert text to numbers. Treat recalculation as one diagnostic check, not a universal repair.

Tips

Use consistent data entry: keep numbers in number cells, place headings outside the data range, and avoid typing currency symbols directly into values when cell formatting can display the currency instead. If you regularly append records, converting the data to an Excel table with Insert > Table can make formulas and ranges easier to maintain as rows are added.

For a quick check, compare a small section of the range with a calculator or temporarily total separate sections. If each section is correct but the combined total is not, inspect the formula range and overlapping cells. If one section is short, focus on text-formatted values, blank-looking cells, or errors within that section.

When troubleshooting a shared workbook, make a copy before changing many imported values or formulas. Keep a note of the original formula if it contains special business logic. When a cell displays a number that seems rounded, inspect the formula bar and cell number format: the displayed value may have fewer decimal places than the stored value, so the underlying total can be accurate even though it looks different.

Troubleshooting

The total is zero or much too low: Select a few cells that should contribute to the result and check whether Excel recognizes them as numbers. Try changing the number format to General; if the value remains left-aligned or a warning indicates text, convert it rather than relying on formatting alone. Also verify the first and last cells in the formula range.

The cell shows #REF!: The formula refers to a cell or range that was deleted or moved. Select the total, inspect the formula bar, and rebuild the reference using the correct current cells. If the workbook was edited by someone else, check whether a source column or worksheet was removed.

The cell shows #VALUE! or another error: Inspect every referenced cell for its own error and review any formulas feeding into the range. If the error comes from a formula in a source cell, fix that formula or its input. A normal SUM can add numeric cells while ignoring text entries, but it cannot produce a dependable numeric total if a referenced cell contains an error value.

The result does not change after editing a number: Confirm that the edited value is inside the formula range and that calculation is Automatic. Press F9 on Windows or use the equivalent recalculation command on Mac. If only one worksheet appears stale, recalculate that sheet and check whether the displayed total is a fixed value rather than a formula.

The total is too high after filtering: If the intended result is for visible rows only, replace SUM with SUBTOTAL using function number 109 for addition. If the total should represent all records, leave SUM in place; the filtered display does not change which rows SUM counts.

Frequently Asked Questions

Why does SUM ignore some numbers?
Those entries may be text rather than numeric values. This often happens when data is pasted or imported. Convert the affected cells to numbers, then check the total again.

Does SUM include hidden rows?
Yes. SUM includes values in hidden rows, including rows hidden by a filter. Use SUBTOTAL with 109 when you need an addition total for visible rows only.

Why does AutoSum give the wrong answer?
AutoSum guesses which neighboring cells belong in the range. Check the highlighted selection before accepting it, especially if there are blank rows, headings, or other totals nearby.

Can blank cells cause a SUM error?
Blank cells in the range are normally treated as zero, so they do not usually cause an error. A cell that looks blank may instead contain a space, text, or a formula returning an empty string; inspect it if the result is unexpected.

Why does my SUM formula show as text instead of calculating?
The formula cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, edit the formula, and press Enter so Excel parses it as a formula. Also check that Show Formulas is not turned on; on Windows, Ctrl+` toggles that display.

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.