In Excel, SUM ignores text in a referenced range and adds the cells it recognizes as numbers. With 10 in A2, the text value 20 in A3, and 30 in A4, =SUM(A2:A4) returns 40—not 60.
What “text” means in an Excel sum
Excel distinguishes a number from text that looks like a number. A cell containing the numeric value 20 can be displayed as text, such as “twenty,” or it can contain the characters “20” stored as text. The last two are text; they may look meaningful or numeric on the worksheet, but Excel does not treat them as ordinary numeric values in every calculation.
This often happens when data is imported from a website, a text file, or another system. An apostrophe typed before a value, such as '20, also tells Excel to store the entry as text; the apostrophe itself is not displayed in the cell. A green triangle in the cell’s corner may indicate a number stored as text, though that indicator can be disabled or may not appear in every situation.
“Sum text” can also mean different things to different people. You might want to add numbers stored as text, total amounts based on a text label such as a product name, or add digits embedded in descriptions. Those are separate tasks, and the right solution depends on which one you mean.
How SUM handles text and other values
When a cell reference or range is an argument to SUM, text in those referenced cells is ignored. So are blank cells and logical values such as TRUE or FALSE stored in the range. Numeric cells are added normally. However, an error value included in the referenced range, such as #N/A or #VALUE!, can make the overall result an error rather than a partial total.
There is a subtle distinction between text in a cell and a text value supplied directly as a function argument. Excel can interpret a numeric text literal entered directly in the argument list differently from text found through a cell reference. That difference is rarely the best way to handle a worksheet, so use actual numeric cells or convert the source values rather than relying on a special case in a formula.
Also, cell formatting is not the same as cell content. Applying the Number format to a text value does not necessarily convert it into a number. Formatting controls how a value is displayed; conversion changes the value Excel calculates with.
Convert text numbers into real numbers
If the cells contain numbers stored as text, converting them is usually the cleanest fix. It lets ordinary totals, averages, charts, and other calculations work as expected without requiring a special formula every time.
- Check the cells. Select a suspicious cell and look for a warning icon or a green triangle. You can also check whether a value is aligned differently from neighboring values, but alignment alone is not definitive because formatting can change it.
- Use the error indicator. In Excel for Microsoft 365 on Windows, select the affected cells, click the warning icon, and choose Convert to Number when that option is offered. The option may not appear if Excel does not identify the contents as numbers stored as text.
- Use Text to Columns for a batch conversion. Select the affected column, choose Data > Text to Columns, keep the default delimited option if the values do not need splitting, and select Finish. Review a copy or a small sample first if the data includes dates, leading zeros, or other formatting that matters.
- Check the total again. After conversion, a basic SUM should include the values. If it still does not, look for spaces, punctuation, errors, or values that are not actually numbers.
Use care with identifiers such as postal codes, product codes, and account numbers. They may contain only digits but still be identifiers, not quantities; converting them to numbers can remove leading zeros or change how they display. They generally should remain text if their digits are labels rather than amounts to calculate.
Use a formula when the source data should stay untouched
Sometimes you need a total without changing the imported column. A formula can convert numeric text as part of the calculation and treat entries that cannot be converted as zero. With text numerals 10, 20, and 30 in A2:A4, =SUMPRODUCT(IFERROR(VALUE(A2:A4),0)) returns 60. VALUE converts text that Excel recognizes as a number, IFERROR substitutes zero for unconvertible entries, and SUMPRODUCT adds the resulting values.
This approach is useful for a report or a one-off calculation, but it does not repair the source cells. If other formulas or charts use the original data, they may still treat those entries as text. For recurring work, cleaning the data at import or converting the source column is often easier to maintain.
A helper column can make the conversion visible and easier to inspect. If A2 contains the text value “ 20 ” with ordinary spaces around it, enter =IFERROR(VALUE(TRIM(A2)),0) in B2; it returns 20. Fill the formula down for the other rows, then sum the helper column. TRIM removes extra ordinary spaces around the value, while IFERROR prevents nonnumeric entries from breaking the helper calculation.
These formulas depend on the characters and regional settings in the source. For example, a value written as 1,234.56 may not be interpreted the same way in every locale. Decimal and thousands separators vary, and currency symbols or unusual spaces can also prevent conversion. If a formula returns zero for a value that should be numeric, inspect the original characters rather than assuming the arithmetic is wrong.
Fix spaces and unwanted characters
Imported values can contain invisible or unexpected characters. Ordinary leading and trailing spaces are often handled by TRIM, but TRIM does not remove every character that can look like a space. Web data may include a nonbreaking space, and copied text can include nonprinting characters. Those can make a value look clean while still stopping Excel from converting it.
For an individual cell, inspect the formula bar and compare the value with a manually retyped version. If you confirm that nonbreaking spaces are the issue, a helper formula can replace them with ordinary spaces before trimming and conversion. With the suspect text in A2, =IFERROR(VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))),0) returns a number when the remaining text is a valid number, or zero when it is not. Test this on representative rows first; replacing characters indiscriminately can change data you meant to preserve.
Be especially cautious when cleaning values that use commas or periods as separators. A comma might mark thousands in one data source and decimals in another. Decide what the source means before replacing punctuation, and check the converted result against known values.
When the text is a label, not a number
If you want to total numeric amounts associated with a text category, do not try to add the label itself. Use a conditional sum. With product names in A2:A5, amounts in B2:B5, and Apple rows containing 4 and 6, =SUMIF(A2:A5,"Apples",B2:B5) returns 10. The text “Apples” is the criterion; the values being added are in the amount range.
For several conditions, SUMIFS can total a numeric range only for rows matching multiple criteria. For instance, you could total sales for one product within one month. The criteria ranges and sum range should cover corresponding rows, and the values in the sum range still need to be numeric if you expect them to contribute to the total.
If a description contains digits—such as “Order 125”—Excel cannot know automatically whether 125 is a quantity, an order identifier, or just part of the description. Extracting digits is a text-parsing task, not a normal sum. Before building a formula, define which digits should count and how to handle descriptions with no number, multiple numbers, or punctuation.
Choose the right fix for the problem
For a dependable workbook, match the solution to the cause rather than forcing every text entry into a number:
- A few numeric-looking cells: use the conversion warning if available, then verify the total.
- A whole imported column: try Text to Columns on a copy or use a controlled import process, paying attention to dates and leading zeros.
- A calculation that must leave source data alone: use a conversion formula, and make clear how nonnumeric entries are handled.
- Amounts associated with names or categories: use a conditional sum such as
SUMIForSUMIFS. - Digits embedded in descriptions: define the extraction rule first; a standard SUM cannot infer the meaning of the text.
For a quick diagnostic, compare the apparent total with a total after converting a small sample. If the difference matches the skipped text-number entries, the issue is likely data type. If it does not, check for duplicates, omitted rows, filters, errors, or values expressed with an unexpected separator. A formula that silently treats unrecognized values as zero can conceal data problems, so consider flagging those entries separately when accuracy matters.
Frequently Asked Questions
Why does Excel SUM return zero when the cells show numbers?
If every apparent number is stored as text, SUM may ignore all of them and return zero. Convert the values to numbers or use a formula that converts numeric text, then verify that the entries use separators Excel recognizes.
Does changing the cell format to Number fix text numbers?
Not necessarily. Number formatting changes display, but it does not reliably convert text that is already in a cell. Use Convert to Number, Text to Columns, or a conversion formula instead.
Can SUM add words such as “ten” or “five”?
No. Excel does not interpret number words as numeric values for SUM. Convert them using a defined mapping or replace them with numbers before calculating; ordinary text labels are ignored in a referenced range.
Why does SUM ignore a number returned by a formula?
A formula can return text that looks numeric, for example when it uses text formatting or joins characters. If the result is text, SUM may ignore it when referenced. Check the formula’s output type and convert it to a number where appropriate.
Why is my SUM result an error instead of a partial total?
A referenced error value such as #VALUE! or #N/A can make the SUM result an error. Find and correct the source error, or deliberately handle errors in a separate calculation; do not hide them without checking what they represent.
Related
- Excel Data Tips and Tricks for Cleaner, Safer Workbooks
- Record Data in Excel Online: Setup, Sharing and Tips
- Gujarati Data in Excel: Typing, Sorting, and Importing
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Page Break Dotted Lines: How to Move Them in PrintYes. In Excel for Microsoft 365 on Windows, open View > Page Break Preview, then drag a dotted…
- Excel Cell Size: Adjust Row Height and Column WidthTo format cell size in Excel for Microsoft 365 on Windows, adjust the row height, the column width,…
- Unmerge Cells in Google Sheets: Shortcut and StepsGoogle Sheets has no dedicated one-keystroke shortcut for unmerging cells. In a browser, open the Tool finder with…
- Schedule an Email in Outlook on Mac: Step-by-StepTo schedule an email in Outlook on a Mac, compose your message, open the menu beside the Send…
- Dotted Line in Excel and Word: How to Add OneTo add a dotted line at the bottom of a cell or selected range in Excel, select it,…
- How to Lock Cells in Excel So You Can ScrollTo keep headings or identifying columns visible while you scroll through a worksheet, use Freeze Panes: select the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.