If Excel’s SUM formula is not working, check that the referenced cells contain numbers, that the formula covers the intended range, and that calculation is set to update. With amounts in B2:B6, enter =SUM(B2:B6) in another cell; if those amounts are 10, 15, 20, 25, and 30, the result should be 100.
What SUM should do
SUM adds numeric values and returns one total. A reference written as B2:B6 means the continuous range from B2 through B6, including both endpoints. SUM can also add separate cells or ranges. It does not usually add numbers that Excel is treating as text, and it does not automatically include data outside the references you specify.
For example, with values in B2, B4, and B6 of 10, 20, and 30, respectively, this formula returns 60: =SUM(B2,B4,B6). Commas separate the arguments in this example, but some regional Excel settings use semicolons instead. If Excel refuses a formula with commas, check the separator section below rather than assuming SUM is unavailable.
A useful distinction: formatting affects how a value looks, while the underlying value and the cell reference determine what SUM adds. A cell formatted as Currency can still contain a number; a cell displaying “25” may instead contain text. The display alone does not always reveal the cause.
Start with the visible symptom
Notice what Excel shows after you press Enter. Different symptoms point to different causes:
- The formula appears as text. The cell may be formatted as Text, or the formula may begin with an apostrophe or a leading space.
- The result is zero or too small. Some referenced entries may be text, the selected range may miss cells, or the entries may be blank.
- The result is an old total. Excel may be set to manual calculation, or a referenced value may not have changed as expected.
- The formula shows an error. Check for a misspelled function, invalid reference, malformed arguments, or a circular reference.
- The answer is larger than expected. The range might include a total row or duplicate values, or a reference may extend farther than the data.
Click the formula cell and look at the formula bar. If you see the formula itself in the cell, inspect its format and entry; if you see a number that seems wrong, inspect the referenced cells and the exact range.
Check for numbers stored as text
Imported data is a common reason SUM skips entries. Values copied from websites, CSV files, or other systems may contain leading apostrophes, spaces, nonbreaking spaces, or currency characters that prevent Excel from recognizing them as numbers. A green triangle in a cell may offer an error-checking option such as converting text to a number, though the indicator does not appear in every case.
Try selecting one suspect cell. If its value is left-aligned while neighboring numbers are right-aligned, that can be a clue, but alignment is not definitive because formatting can change it. You can also test a small sample by changing its number format or using Excel’s error-checking menu. Changing a cell to Number format alone does not convert text into a numeric value; it changes how numeric values would be displayed.
For a clean text number in A2, a separate cell can convert it with =VALUE(A2). If A2 contains the text 25, the result is the number 25. This is useful for a quick check or a conversion column, but it may fail when imported text contains unexpected symbols or spaces. Clean the source consistently, then copy and paste the converted results as values if you want to replace the original data.
When the entries are consistently numbers stored as text, another option is Excel’s Convert to Number warning where available. For a column of imported values, select the cells and use Data > Text to Columns, then complete the wizard without changing the delimiter if the data is already in separate cells. Review the result before replacing original data, especially if the values include leading zeroes such as product codes; those codes may need to remain text.
Verify the range and hidden rows
Click the total and inspect the colored outlines Excel places around referenced cells while you edit the formula. Confirm that the first and last rows are correct and that the range does not stop one row early. If new records are added below a fixed range, they may not be included. Converting a dataset to an Excel Table can help formulas expand as rows are added, but check the resulting references rather than assuming a current total includes every new row.
Hidden rows are another source of confusion. SUM includes values in hidden rows if they are inside its referenced range. Filtering a list does not make an ordinary SUM ignore the filtered-out records. If you want a total that responds to filtering, and the amounts are in B2:B10, use =SUBTOTAL(9,B2:B10). With visible amounts of 10, 20, and 30, and other rows filtered out, the result is 60. Function number 9 means SUM; SUBTOTAL ignores rows filtered out by a filter. Manually hidden rows are treated differently by function number 9; function number 109 excludes manually hidden rows as well.
Watch for double-counting, too. If a range includes detail values and a previously calculated subtotal, adding the whole range counts the underlying values and the subtotal again. For instance, keep the total outside the range of detail rows, or deliberately exclude any subtotal row from the reference.
Make sure Excel is recalculating
If a formula result does not change after you edit an input, check calculation mode. In Excel for Microsoft 365 on Windows, open the Formulas tab and choose Calculation Options > Automatic. Excel for Mac has calculation options on the Formulas tab as well; menu wording and placement can vary slightly by release. In Excel for the web, recalculation is generally handled automatically, and the desktop calculation setting may not be available in the same way.
Manual calculation can be useful in very large workbooks, but it means formulas may show results from the last calculation until recalculated. After switching to Automatic, edit an input cell and confirm the total updates. If only one workbook behaves this way, also check for formulas linked to another workbook that is closed or has not refreshed; external links can affect the values Excel uses.
Another possibility is that Excel is displaying formulas instead of their results. On Windows, press Ctrl+` (the grave-accent key, often near the top-left of the keyboard) to toggle Show Formulas, or use Formulas > Show Formulas. On Mac, use Formulas > Show Formulas if the keyboard shortcut differs on your keyboard. If just one cell displays the formula, select it, change its format from Text to General using Home > Number Format, then re-enter the formula by pressing F2 and Enter on Windows. On Mac, use the equivalent edit-and-confirm keys for the keyboard layout.
Read errors and references carefully
SUM can return an error if a referenced cell contains an error such as #VALUE! or #N/A. Find the error in the referenced range and investigate its source; replacing the SUM formula will not necessarily resolve the underlying problem. A #REF! message means a reference is invalid, often because a referenced row, column, or sheet was deleted. Rebuild the reference to point to the intended cells.
A circular reference occurs when a formula refers, directly or indirectly, to its own result. If the total is in B10, for example, including B10 in the range being totaled can create a circular reference. Keep a total outside its input range. Excel may warn about circular references, and its status bar or error-checking tools can help locate them.
Check sheet names and punctuation if the values are on another worksheet. A reference to a sheet with spaces in its name needs apostrophes around the sheet name, for example =SUM('January Sales'!B2:B6). This assumes the values are in B2:B6 on the worksheet named January Sales; if they add to 100, the formula returns 100. The apostrophes around the sheet name are part of Excel reference syntax, not a sign that the values are text.
Check separators and formula entry
Excel’s argument separator depends on regional settings. A formula that uses commas in one installation may need semicolons in another. SUM with a single range has no argument separator, so it is a simple test of whether the function itself works. If a multi-argument formula is rejected, follow the separator Excel inserts when you select function arguments, and check your operating system’s regional list-separator settings if necessary.
Make sure the formula begins with an equals sign and has balanced parentheses. Avoid copying typographic “smart” quotes into formulas that require straight quotation marks. For example, if regions are listed in A2:A10 and amounts in B2:B10, this formula adds the amounts for rows marked East: =SUMIF(A2:A10,"East",B2:B10). If the matching East rows contain 12, 18, and 20, the result is 50. If the criterion or range is wrong, the formula can return zero without indicating a syntax error.
A reliable troubleshooting sequence
Work through the checks in order; changing one thing at a time makes the cause easier to identify.
- Select the formula cell and confirm the formula begins with an equals sign and points to the intended cells.
- Check whether the cell is formatted as Text or whether Show Formulas is turned on. Correct the setting and re-enter the formula if needed.
- Inspect a few source cells for numbers stored as text, stray spaces, error values, or blanks.
- Check the range boundaries and look for hidden, filtered, duplicated, or subtotal rows that affect the result.
- Set calculation to Automatic, then change a source value to see whether the result recalculates.
- If Excel rejects the entry, verify the regional argument separator and inspect parentheses, references, and sheet names.
For a quick isolation test, enter a SUM formula over a small range of cells you can verify manually. If that works, the function is operating and the problem is likely in the original data or references. If Excel displays the formula as text or rejects the entry, focus first on formatting, the leading equals sign, and separators.
When another function is a better fit
Use SUM when every numeric value in the specified references should count. Use SUBTOTAL when a filtered list needs a total that responds to filtering. Use SUMIF or SUMIFS when only rows matching one or more conditions should count; these functions are not fixes for text values or incorrect ranges, so confirm the criteria and source data.
For a filtered list, consider whether manually hidden rows should count as well as filtered-out rows before choosing a SUBTOTAL function number. For conditional totals, confirm that labels match the criteria exactly, including extra spaces and spelling. When the same calculation is repeated across a report, an Excel Table and structured references can make the formula easier to maintain as records are added, though a quick check of the table’s actual data range is still worthwhile.
Frequently Asked Questions
Why does SUM return 0 when the cells look like numbers?
Those entries may be numbers stored as text, or the formula may refer to blank or incorrect cells. Check a source cell’s content, convert clean numeric text, and verify the range.
Why does my SUM formula show instead of the answer?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Change the format to General and re-enter the formula, or turn off Show Formulas if formulas across the sheet are visible.
Does SUM ignore filtered rows?
No. SUM includes numeric cells within its range even when rows are filtered out. Use SUBTOTAL with function number 9 when the total should reflect filtered visibility.
Why does Excel say there is a problem with this formula?
Common causes include using the wrong regional separator, missing parentheses, an invalid reference, or pasted smart punctuation. Try a simple single-range SUM first and then rebuild the more complex formula using Excel’s local separator.
Why is my total not changing when I edit a value?
Check that calculation is set to Automatic and that the edited cell is actually included in the formula’s range. Also check whether the formula points to another sheet or workbook rather than the cell you changed.
Related
- Randomize Numbers in Excel Without Duplicates
- Rank in Excel: How to Rank Values Largest to Smallest
- Randomize Names in Excel Without Repeats: Easy Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel CHOOSE Function Not Working? Common FixesIf Excel’s CHOOSE function is not working, first check that its index is a whole number from 1…
- Excel Row Numbers Missing? Causes and How to Restore Them in ExcelIf Excel’s row numbers have disappeared, first turn row headings back on: in Excel for Microsoft 365 on…
- Excel Scatter Plot Not Plotting X Values? Here’s How to FixIf an Excel scatter plot is not plotting your x values, first confirm that the chart type is…
- Excel #NUM! Error: Causes, Meaning, and How to Fix ItExcel’s #NUM! error means a formula has an invalid numeric value or cannot complete a calculation with the…
- VLOOKUP Not Working Unless I Click the Cell?If VLOOKUP appears wrong until you click into its cell and press Enter, Excel is usually not recalculating…
- How to Extend a Filter Range in Excel Without Missing RowsTo extend a filter range in Excel, include all the data rows and columns in the range, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.