If an Excel SUM formula is not calculating correctly, check that the formula references the intended cells, the values are real numbers, and calculation is set to Automatic. In Microsoft 365 for Windows, start with the formula bar and the Formulas tab; the cause is often a text-formatted number, an incorrect range, or a formula cell stored as text.
What SUM does—and what it leaves out
SUM adds numeric values in the cells or ranges you specify. It does not add every value that looks like a number: text in a referenced cell is generally ignored, while an error value in the range can make the result an error. Blank cells are ignored. These rules explain why a total can be lower than expected without Excel showing an obvious warning.
For a basic example, suppose B2 contains 12, B3 contains 18, B4 contains 25, and B5 contains 10. With those values in B2:B5, the formula =SUM(B2:B5) returns 65. If you expected a different total, first compare the range in the formula with the cells that actually contain the values. A formula can be syntactically valid and still omit the last row or include an unrelated one.
Excel updates results when its calculation settings allow it to recalculate, but SUM does not know which rows you intended to include or whether a text entry should count. It follows the references and data types it receives.
Check the formula and selected range
Select the cell containing the total and inspect the formula bar. Confirm that the formula starts with an equals sign and that the range covers the complete list, including any newly added rows. If values are in B2 through B20, a formula that stops at B19 will leave out B20. A formula that starts at B3 will omit B2.
For example, if monthly amounts are in B2:B5, =SUM(B2:B5) totals those four cells. The range must match the actual layout; do not use that example unchanged if your data is elsewhere. Click the range shown in the formula bar, or edit the formula and select the intended cells on the sheet. Watch for a misplaced colon, a missing cell, or a reference to a different worksheet.
Also look for an accidental extra argument or a formula that has been edited to include only selected cells. If the data is in a continuous block, selecting that block while creating the formula reduces the chance of skipping a row. If you add rows later, consider converting the list into an Excel Table so references can expand with the table.
Convert numbers stored as text
A common import problem is numbers stored as text. They may look like ordinary amounts, but SUM can ignore them when they are in a referenced range. Clues include values aligned differently from the other numbers, a green warning indicator, or a leading apostrophe visible when you select the cell. Copying data from a website, CSV file, or another system can introduce this issue.
To check, select a suspicious cell and look at the formula bar and the warning options. If Excel offers Convert to Number, use it after confirming the value is meant to be numeric. You can also select the affected cells and choose Data > Text to Columns, then select Finish, which often converts consistently formatted text values. Check the result afterward, especially when the data includes dates, decimal marks, or thousands separators.
For a one-cell conversion, assume the text value is in B2. The formula =VALUE(B2) returns its numeric equivalent when Excel can interpret the text as a number. Copy the result and use Paste Special > Values if you want to replace the original text with converted values. The interpretation of decimal and thousands separators can depend on regional settings, so verify a few converted entries before relying on a full-column conversion.
Make sure the formula cell is not formatted as text
If the cell displays the formula itself instead of a result, the cell may be formatted as Text, or the formula may have been entered with a leading apostrophe. Select the cell, choose Home > Number Format > General, then press F2 and Enter to re-enter the formula. On a Mac, use the same ribbon steps; keyboard behavior can vary with the keyboard’s function-key settings.
Another possibility is that Show Formulas is turned on. In Excel for Windows, go to Formulas > Formula Auditing > Show Formulas to toggle it off. When this view is on, Excel displays formulas in worksheet cells instead of their calculated results, which can make every formula appear broken. The column may also widen because the formula text is longer than the displayed result.
Check for a leading space or apostrophe before the equals sign, too. Excel treats that entry as text rather than as a formula. Re-entering the formula in a cell set to General usually resolves this, provided the formula itself is correct.
Check calculation settings and refresh the result
When a formula appears correct but its result does not change after you edit an input, Excel may be using manual calculation. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. Then edit an input again or recalculate. Manual mode can be useful in very large workbooks, but it means formula results may remain unchanged until calculation is triggered.
To recalculate a worksheet, press Shift+F9 on Windows. To force a full recalculation, press Ctrl+Alt+F9. Laptop function-key settings may require holding Fn as well. On Mac, use Formulas > Calculate Now or the equivalent calculation command available in your Excel version; keyboard shortcuts can differ by keyboard and settings. If the workbook is in Automatic mode and only one result is stale, editing the formula or changing a referenced value can also prompt an update.
If Automatic is selected and the result still seems wrong, check whether a formula depends on another formula that has not updated, or whether calculation is being interrupted by a circular reference. A circular reference occurs when a formula refers to its own cell directly or through a chain of other cells. Excel generally warns about circular references; inspect the referenced cells rather than repeatedly forcing recalculation.
Distinguish a wrong total from a display issue
Sometimes the stored result is correct but the displayed number is rounded. For example, a cell may show 10.00 while holding a more precise value. Summing underlying values can therefore produce a total that differs slightly from the sum of the rounded figures you see on screen. Select the cells and use Home > Increase Decimal to inspect more digits. Number formatting changes what is displayed, not the underlying value.
Floating-point arithmetic can also produce very small precision differences in calculations involving decimals. For ordinary totals, this is usually not the main cause of a large discrepancy. If a process requires rounding each line before adding it, that is a different calculation from adding full-precision values and rounding only the final total. Confirm the intended accounting or reporting rule before changing formulas.
Errors in any referenced cell are another reason a total may show an error rather than a number. Find the error cell and correct its cause; hiding it with an error-handling formula can conceal an underlying data problem. If you need a total that excludes certain rows, make that rule explicit rather than assuming SUM will omit them.
Understand filtered rows and conditional totals
SUM includes numbers in hidden rows and in rows removed from view by a filter. Filtering changes what is displayed, not what SUM adds. This often surprises people who filter a list to one category and expect the total to include only the visible records.
If the amounts are in B2:B100 and you want a subtotal that responds to filtering, use =SUBTOTAL(109,B2:B100). This layout assumes the values to total are in B2:B100; function number 109 sums visible cells while excluding manually hidden rows as well as rows removed by a filter. Verify the result against the visible records if hidden rows matter to your report.
For a total based on a condition, use a conditional summing function such as SUMIF or SUMIFS instead of adding everything and hoping the filter changes the calculation. For example, a category-specific total needs both a category range and a sum range. Keep the ranges the same size and confirm that category labels match exactly, including any extra spaces or inconsistent spelling.
A practical troubleshooting sequence
Work through these checks in order so you can identify the cause rather than changing several things at once:
- Select the total cell and read the formula bar. Confirm the formula, worksheet references, and first and last cells in the range.
- Check several entries that appear to be missing from the total. Confirm they are numeric values, not numbers stored as text.
- Look for error values in the referenced range and resolve their source.
- Confirm the formula cell is set to General or an appropriate number format, and turn off Show Formulas if it is enabled.
- Set calculation to Automatic, then recalculate if the displayed result is stale.
- Decide whether hidden or filtered rows should count. Use a suitable subtotal or conditional formula if they should not.
- Compare the result with a small hand-checked sample, then verify the entire range before using the total in a report.
This sequence also helps separate a formula problem from a data problem. If the range and calculation mode are correct but the total remains off, inspect the data source for duplicates, blanks, imported text, or values entered in a different unit.
Frequently Asked Questions
Why does Excel SUM return zero? Check that the referenced cells contain numbers rather than text and that the formula points to the intended range. If the formula is displayed instead of evaluated, check the cell’s Text format, a leading apostrophe, and Show Formulas.
Why is SUM ignoring some numbers? Those entries may be stored as text, outside the selected range, or in cells that contain something other than numeric values. Select a missing entry and inspect its warning indicator and formula bar, then convert it if it is meant to be a number.
Does SUM ignore filtered or hidden rows? No. SUM normally includes numeric values in hidden and filtered-out rows. Use SUBTOTAL with function number 109 when you want a total of visible cells that also excludes manually hidden rows.
Why does the formula show in the cell instead of the answer? The cell may be formatted as Text, the entry may start with an apostrophe, or Show Formulas may be on. Set the cell to General, re-enter the formula, and check Formulas > Formula Auditing > Show Formulas.
Why does my total not update when I change a value? Check Formulas > Calculation Options and select Automatic. If the workbook is set to Manual, recalculate the worksheet or workbook after making changes.
Why does my total differ slightly from the visible numbers? The cells may contain more decimal places than their number format displays. Increase the displayed decimal places and determine whether the intended method is to add full-precision values or round each entry before summing.
Related
- Excel Scatter Plot: How to Change X and Y Axes Correctly
- Excel Return Table Data as JSON: Methods and Limits
- Excel Repeatedly Not Responding? Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel MATCH Not Working? Fix #N/A and Wrong ResultsIf Excel’s MATCH function returns #N/A or the wrong position, first check its third argument: use 0 for…
- Excel Error 0xc0000142: Causes, Fixes, and Next StepsExcel error 0xc0000142 means Windows could not initialize a component Excel needs while starting the program. Close and…
- Excel File Too Large? How to Find and Fix It in ExcelIf your Excel file is too large to open, save, email, or share, first work out whether the…
- VLOOKUP Excel Troubleshooting: Fix Common Formula ErrorsTo troubleshoot VLOOKUP in Excel, check that the lookup value is in the first column of the selected…
- Excel FILTER #VALUE! Error: Causes and FixesIn Excel, a FILTER formula usually returns #VALUE! when its include array is not the same height or…
- Excel SUMIF Spill Error: Causes and How to Fix It QuicklyTo fix an Excel SUMIF #SPILL! error, either clear the cells blocking the formula’s output or change the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.