If Excel is not displaying a sum, first select the numeric cells: Excel for Microsoft 365 on Windows normally shows their total as Sum on the status bar at the bottom. If Sum is missing, right-click the status bar and select Sum; if a worksheet formula is not returning a result, check that the formula is entered correctly, the cells contain numbers, and calculation is set to Automatic.
Two different places Excel can display a sum
The phrase “not displaying sum” can describe two separate problems. Excel can show a quick total on the status bar without placing anything in a worksheet cell, or a formula such as SUM can calculate a result in a cell. The fix depends on which display you expect.
The status-bar total is useful for a quick check: select several amounts and read Sum at the bottom of the Excel window. It does not change the workbook or insert a formula. A worksheet formula, by contrast, remains in a cell and updates when its referenced values change. If you need a total to print, share, or use in another calculation, enter a formula in a cell.
For example, with values 12, 8.5, and 4.5 in B2:B4, the selected cells should total 25 on the status bar. To keep that total in the sheet, with those same values in B2:B4, enter =SUM(B2:B4) in B5; the result is 25.
Turn on Sum on the status bar
In Excel for Microsoft 365 on Windows, right-click the status bar along the bottom edge of the window. In the list of available calculations, click Sum so it has a check mark. Then select the cells you want to total. The status bar can also display other quick calculations, such as Average and Count, and you can enable or disable those options in the same menu.
If the total still does not appear, check what you selected. Sum is calculated for numeric values, not ordinary text. Selecting a heading, an empty cell, or cells containing numbers stored as text may produce no useful total. Try selecting only the cells that contain the amounts. If selecting a range that mixes numbers and text, Excel totals its numeric values and leaves text out.
On Excel for Mac, select the cells and look along the bottom status bar. If Sum is not shown, Control-click or right-click the status bar to check the available calculation options; the wording or presentation can vary slightly by version. Excel for the web also has a status bar, but its options and layout may differ from desktop Excel. If you cannot find a status-bar setting in your version, use a SUM formula in a worksheet cell instead.
Check that the worksheet formula is entered correctly
A SUM formula needs an equals sign, the function name, and a valid range or list of cell references. A common example is =SUM(B2:B20). The colon means every cell from B2 through B20. For a range that is not continuous, separate the references with commas, as in =SUM(B2:B10,D2:D10). In Excel’s usual English-language settings, those commas separate arguments; some regional settings use different list separators.
Check the formula bar after selecting the cell that should contain the result. If the formula appears there but the cell looks empty, the cell may have a display format that hides its value, or the formula may return an empty text string. If the cell displays an error such as #NAME? or #VALUE!, the error itself is a clue: inspect the spelling, punctuation, range, and referenced cells. If you see the formula characters inside the cell instead of a result, use the display checks below.
Make sure the range includes the rows you intend to add. A formula in B21 using B2:B20 will not include a new amount entered in B21. To avoid accidentally omitting later rows, a formatted Excel Table can be useful: formulas that refer to a table column can include rows as the table grows. For a straightforward fixed list, however, a clearly checked range is often all you need.
When a cell shows the formula instead of its result
If many cells show formulas rather than calculated values, Show Formulas may be turned on. On Windows, go to Formulas > Show Formulas to switch it off. The Windows shortcut is Ctrl+` (the grave-accent key, often near the top-left of the keyboard). On a Mac, use Formulas > Show Formulas; keyboard shortcuts can differ by keyboard layout, so the ribbon command is the clearest option.
If only one cell displays a formula as text, check its number format. Select the cell, choose Home > Number Format and select General, then press F2 followed by Enter to make Excel re-evaluate the entry. On Windows, F2 edits the active cell; on some Mac keyboards you may need to hold Fn while pressing F2. If the formula was entered while the cell was formatted as Text, changing the format alone may not convert the existing entry: re-enter it or use the F2-and-Enter method.
A leading apostrophe can also make an entry text. For instance, an entry beginning with an apostrophe followed by =SUM(B2:B4) is treated as text rather than as a formula. Remove the apostrophe and enter the formula again. If the cell displays a leading apostrophe or the formula remains unchanged after changing the format, inspect the formula bar for this issue.
Check for numbers stored as text
A cell can look like it contains a number while Excel treats its contents as text. This often happens after importing data from a website or a text file, or when a value includes a leading apostrophe, spaces, or an unexpected character. SUM ignores text entries in a referenced range, so a formula can return a total that is lower than expected without producing an error.
Look for warning indicators in the cells, or test a suspect value in a separate cell by checking whether it behaves like a number in a calculation. If Excel offers a warning menu for a selected cell, Convert to Number may be available. For a small set of entries, retyping the values as numbers can be the simplest solution. For a larger imported column, inspect the source and clean the data carefully before relying on its total; blindly removing characters could change meaningful values such as account codes or identifiers.
Also distinguish numeric values from numeric-looking labels. A product code such as 00127 may need to remain text so its leading zero is preserved, even though it resembles a number. Do not convert every text cell merely to make a total appear. Convert only entries that represent quantities or amounts you actually intend to add.
Make sure Excel is recalculating
If the formula is correct but its result does not change after you edit an input, check the calculation setting. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. In Manual mode, Excel may wait for a recalculation command rather than updating formulas whenever an input changes. Press F9 to recalculate open workbooks on Windows; on a Mac, the shortcut can differ, so use the Formulas ribbon or Excel’s calculation commands if needed.
Calculation mode matters when a workbook is large or contains complex formulas, because manual calculation can be chosen to control when calculations run. For an ordinary list of values, Automatic is generally the expected setting. After changing it, check the total again and confirm that the cells feeding the formula contain the current values.
Filtered rows, hidden rows, and totals
A SUM formula normally includes numeric values in hidden rows and rows excluded by a filter if those cells are still inside its range. That can make its result differ from the visible rows on screen. The status-bar Sum also totals the selected numeric cells, so selecting a range that includes rows you did not mean to count can be misleading.
If you need a total that responds to filtering, use SUBTOTAL rather than assuming SUM will ignore filtered-out rows. With values in B2:B20, enter =SUBTOTAL(109,B2:B20) in a cell outside the range; it totals visible numeric values and excludes rows hidden by a filter or hidden manually. The 109 option is the sum operation that also ignores manually hidden rows. Choose the range carefully and keep the result cell outside it to avoid a circular reference.
This is not a general replacement for SUM. If the goal is to total every value in a list regardless of filtering or hidden rows, SUM is appropriate. If the goal is to show a subtotal for only the rows currently visible, SUBTOTAL is designed for that situation.
A quick troubleshooting order
When you are unsure which issue is responsible, work through the checks in this order. It separates a missing status-bar option from a formula or data problem and helps avoid changing unrelated settings.
- Select only the cells you mean to add and check the status bar. If Sum is absent, right-click the status bar and enable it.
- If you need a saved total, enter a SUM formula in a cell and verify that its range includes all intended values.
- If the formula itself is visible, turn off Formulas > Show Formulas. If only one cell is affected, check its number format and any leading apostrophe.
- If the total is too low, inspect the input cells for numbers stored as text, blank cells, or an incorrect range.
- If the result stays stale after an edit, set calculation to Automatic and recalculate.
- If you are totaling a filtered list, decide whether hidden rows should count; use SUM for all values or SUBTOTAL for visible values.
Frequently Asked Questions
Why does Excel show Average but not Sum? The status bar can show several calculations independently. Right-click it and select Sum, then select the numeric cells again. If Sum is enabled but still absent, check whether the selected cells actually contain numbers.
Why does my SUM formula return zero? The referenced cells may be blank, contain text rather than numbers, or fall outside the range you intended. Check the formula bar and inspect the inputs. If values were imported, convert only the entries that are genuine numeric amounts.
Why is Excel displaying the SUM formula instead of the answer? Show Formulas may be enabled, or the individual cell may be formatted as Text or have a leading apostrophe. Turn off Formulas > Show Formulas; for a single affected cell, choose General and re-enter the formula.
Does SUM include filtered-out rows? Yes. SUM normally includes numeric cells in its referenced range even when their rows are filtered out or hidden. Use SUBTOTAL with function number 109 when you want a total of visible values that also ignores manually hidden rows.
Can I see a sum without adding a formula to the sheet? Yes. Select the numeric cells and read Sum on the status bar, enabling it through the status-bar context menu if necessary. This quick calculation is only a display; it does not create a result cell in the workbook.
Related
- Remove a Data Model from Excel: Steps and Effects
- Excel Data Modeling: How It Works and When to Use It
- Excel Data Modelling: Tables, Relationships and Pivots
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Column Filter Not Working? Fixes That HelpIf an Excel column filter is not working, select a cell in the dataset and choose Data >…
- Excel Print Area Not Working? Common FixesIf Excel is printing the wrong cells or ignoring a print area, select the intended range and choose…
- XLOOKUP Not Working? Common Causes, Checks, and FixesIf XLOOKUP is not working, first check that your Excel version supports it, then verify the lookup value…
- Excel Goal Seek Not Working? Fix These Common ProblemsIf Excel Goal Seek is not working, first confirm that the Set cell contains a formula linked to…
- Excel Sheet Hyperlink Not Working? How to Fix ItIf an Excel hyperlink seems unresponsive, first try Ctrl+clicking its cell on Windows: Excel normally uses a regular…
- Excel Workbook Won’t Scroll? Fix Frozen or Stuck Sheets FastIf an Excel workbook will not scroll, first go to View > Freeze Panes > Unfreeze Panes, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.