If Excel is not giving the correct sum, first check that the formula includes the intended cells, that the values are real numbers, and that calculation is set to Automatic. In Microsoft 365 for Windows, select Formulas > Calculation Options > Automatic, then compare the formula’s range with the cells you meant to add.
Start by checking the formula and its range
A sum that looks wrong often comes from a formula that is working exactly as written but covers the wrong cells. Click the result cell and inspect the formula in the formula bar. Check the first and last cell references, and look for omitted rows, an extra header row, or a range that stops before the newest data.
For example, with amounts in B2:B6—10, 20, 30, 40, and 50—the formula =SUM(B2:B6) returns 150. If the actual data continues through B7, the formula does not include that final entry. If you insert or append rows outside a fixed range, the formula may not expand to include them.
Also check whether the formula refers to the intended worksheet. A reference such as =SUM(Sheet2!B2:B6) adds cells on Sheet2, not the active sheet. If the formula was copied from another row or column, relative references may have shifted. Press F2 to edit the selected formula and use the arrow keys to inspect its references. Press Enter when you are done.
- Compare the highlighted range with the full block of intended values.
- Check for rows above, below, or between the selected cells that were missed.
- Confirm that the formula points to the correct sheet and column.
- Look for a leading apostrophe or an unexpected cell reference in the formula bar.
Make sure Excel is recalculating
Excel can display an old result if calculation is set to Manual. This setting is useful in large workbooks where recalculating every formula takes time, but it can make a changed input appear to have no effect. On Windows, select Formulas > Calculation Options > Automatic. Then press F9 to recalculate open workbooks. Ctrl+Alt+F9 forces a full calculation of all formulas.
On Excel for Mac, calculation settings are available from the Formulas tab, and you can use its Calculate Now command to recalculate. Keyboard shortcuts can differ with the Mac keyboard and function-key settings, so use the ribbon command if a Windows shortcut does not work. Excel for the web generally recalculates formulas automatically, though a browser refresh or reopening the workbook can help distinguish a display issue from an incorrect formula.
Calculation mode can affect more than one formula, so do not assume a single sum is broken just because its displayed result is stale. Change an input cell and see whether other dependent formulas update. If they do not, check calculation settings before rewriting formulas. When a workbook contains volatile formulas or links to other files, a full recalculation may take longer or produce updated results that differ from a previously saved view.
Find numbers stored as text
A cell can look like a number while Excel stores it as text. This can happen after importing a CSV, copying data from a website, or entering a value with a leading apostrophe. The text may be left-aligned, and a green warning indicator may appear, but neither clue is guaranteed. A normal SUM formula ignores text values in a referenced range, so some entries can be missing from the total.
Suppose B2:B4 contains the numeric values 12 and 18, plus the text value 25. The formula =SUM(B2:B4) returns 30, not 55. Select a cell with a warning indicator and choose Convert to Number if Excel offers that option. For a whole imported column, select the cells and use Data > Text to Columns, then complete the wizard with the appropriate format. You can also copy a blank cell, select the affected cells, and use Paste Special to multiply by 1, but check a copy first because conversion can change how unusual values are interpreted.
With the text number 25 in B2, the formula =VALUE(B2) returns the numeric value 25 in the formula cell. VALUE is handy for an isolated entry, but converting the source data itself is usually clearer when you need to sort, filter, chart, or reuse the values. Be careful with decimal and thousands separators: imported data that uses a different regional convention may require a deliberate import setting rather than a blanket conversion.
Understand filters and hidden rows
The ordinary SUM function adds values in its referenced range whether rows are visible or hidden. That means a filtered list can display a total that includes records currently filtered out. This is not an arithmetic error; it is a difference between summing all records and summing only the visible records.
With sales in B2:B10, =SUBTOTAL(9,B2:B10) adds the values that remain visible after a filter is applied. The function number 9 means SUM. To exclude rows hidden manually as well as rows removed by a filter, use =SUBTOTAL(109,B2:B10). The distinction matters when someone hides rows without applying a filter. SUBTOTAL also ignores other SUBTOTAL results within its range, which helps avoid double-counting nested summaries.
Choose the function that matches the question you are asking. Use SUM for the full dataset even when some rows are hidden. Use SUBTOTAL when a report should change with the visible filtered records. Before sharing a total, label it clearly—for example, Total of all records or Total of visible records—so readers know what it represents.
Check rounding and number formatting
A displayed total can differ from the apparent sum because Excel stores more decimal places than the cell shows. Formatting changes what you see, not the underlying value. If B2:B4 contain 10.004, 10.004, and 10.004 but display as 10.00 each, =SUM(B2:B4) returns 30.012. The result cell might display 30.01 when formatted to two decimal places. Adding the three displayed figures by eye gives 30.00, but those displayed figures are rounded views of the stored amounts.
With those three values in B2:B4, =ROUND(SUM(B2:B4),2) returns 30.01, the total rounded to two decimal places. That is different from rounding each input first and then adding the rounded entries; the right approach depends on the rules for the data. For prices, measurements, or other values, decide whether the underlying values should retain precision or whether each item must be rounded before the total is calculated. Do not round merely to make a result match what is displayed.
To inspect the underlying precision, select a cell and look at the formula bar, or temporarily increase the displayed decimal places using Home > Increase Decimal. Number formats such as currency and percentage can also make a value look unexpected: a value of 0.5 formatted as a percentage displays as 50%, not 0.5. Formatting does not convert a text entry into a number.
Look for errors, blanks, and unintended values
If a cell in the sum range contains an error such as #VALUE! or #N/A, SUM may return that error rather than a numeric total. Find the source cell and correct the underlying formula or data. Hiding the error is not the same as fixing it; a formula that suppresses errors can make a report look complete while leaving a missing or invalid record unresolved.
Blank cells are normally treated as zero by SUM. A cell containing a formula that returns an empty string may look blank, but it is not identical to a truly empty cell. Text labels and text numbers are not added as numeric amounts. Check for unexpected negative values, duplicate entries, or subtotals already included among the detail rows. These issues can make a correct formula produce a total that is wrong for the intended task.
For a quick independent check, select the cells in the range and look at Excel’s status bar at the bottom of the window. If Sum is not visible, right-click the status bar and select Sum. The status-bar total is useful for comparison, but it follows the selection and visibility behavior of Excel; it does not replace checking the source data or the formula range.
Make totals easier to maintain
For a growing list, an Excel table can reduce the chance that new rows fall outside a fixed range. Select the data and choose Insert > Table, confirming whether the first row contains headers. If the table is named Table1 and its amount column is named Sales, =SUM(Table1[Sales]) totals that table column and normally includes rows added to the table.
Tables are especially useful for records that are appended over time, but they do not correct text numbers, duplicate transactions, or an unsuitable definition of the total. Review the column heading and the records included before relying on a structured reference. If you need a total that responds to filtering, use SUBTOTAL with the table column rather than assuming an ordinary SUM will change when rows are hidden.
When a formula is copied, use absolute references only when they are needed. For example, dollar signs lock a reference when copying a formula, but they do not repair an incorrect range. A simple audit habit helps: select the total cell, inspect the formula, and trace a few inputs back to their source before distributing the workbook.
A practical troubleshooting order
Work through these checks in order, changing one thing at a time. This makes it easier to identify the cause instead of masking it with a different formula.
- Click the result and inspect the formula and its referenced cells.
- Confirm that the range includes all intended records and excludes unrelated cells.
- Set calculation to Automatic and recalculate if the value appears stale.
- Check whether any apparent numbers are stored as text.
- Decide whether filtered or manually hidden rows should count.
- Inspect decimal precision, cell formats, errors, duplicates, and negative values.
- Compare the result with the status-bar Sum or a separately checked small sample.
If a workbook came from another person or system, ask what the total is intended to represent: all rows, visible rows, rounded line items, or unrounded amounts. Many apparent spreadsheet mistakes are disagreements about that definition rather than failures in Excel’s addition.
Frequently Asked Questions
Why does Excel SUM skip some cells?
SUM adds numeric values in its referenced range. It skips text, including numbers stored as text, and it does not include cells outside the range. Check the cell values and formula references.
Why is my Excel total different from the numbers I see?
Cells may contain more decimal places than their formatting displays. Increase the displayed decimal places or inspect the formula bar. If the inputs must be rounded before addition, use a rounding method that matches the applicable rules.
Does SUM include filtered-out rows?
Yes. SUM includes values in hidden or filtered-out rows when they are in its range. SUBTOTAL with function number 9 sums visible filtered results; number 109 also excludes manually hidden rows.
How do I make Excel update a sum?
On Windows, choose Formulas > Calculation Options > Automatic and press F9. If dependent formulas still appear stale, Ctrl+Alt+F9 forces a full recalculation. On Mac, use the Formulas tab’s Calculate Now command if needed.
Can cell formatting fix a wrong sum?
No. Formatting changes how a value is displayed, not whether text becomes a number or which cells a formula includes. Convert text numbers and correct the formula or source data separately.
Related
- Excel Middle Years Academy: What Families Should Know
- Excel Middle Row Selection: Find and Select the Center Row
- Excel Power Query: Combine Columns Without Losing Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- XLOOKUP Excel Not Working? Fix Common Errors and CausesIf XLOOKUP is not working, first check that your Excel version supports it, then confirm the lookup and…
- Text Wrap Not Working in Word? Fix Image Text FlowIf text will not wrap around a picture in Word, select the picture and choose Picture Format >…
- INDEX MATCH Not Working? Fix Common Lookup ErrorsIf INDEX MATCH works for some values but not others, the usual cause is that Excel sees the…
- Excel #VALUE! Error: Common Causes, Checks, and FixesExcel’s #VALUE! error means a formula received a value or data type it cannot use, such as text…
- VLOOKUP Not Working? Common Causes and FixesWhen VLOOKUP does not work, it is usually because the lookup value is not an exact match, the…
- Hyperlink Not Working in Word? Fixes to TryIf a hyperlink in Word is not working, first try Ctrl+clicking it on Windows; Word often requires that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.