Yes, the SUM function in Excel automatically ignores text and blank cells within the range you give it. For example, =SUM(A1:A10) adds only the numeric values in that range and skips any cells containing text, logical values (TRUE/FALSE), or errors. If you need to sum numbers that are stored as text (e.g., values with apostrophes or spaces), use SUMPRODUCT with VALUE or the SUMIF function with a criteria like "<9.99E+307".
How SUM Handles Text by Default
Excel's SUM function is designed to add numeric values only. When you reference a range, it evaluates each cell and includes it in the total only if it contains a number. Text strings, logical values (TRUE/FALSE), and empty cells are ignored. This behavior is consistent across all modern versions of Excel, including Microsoft 365, Excel 2021, 2019, and earlier. For example, if A1 contains 5, A2 contains "apple", and A3 contains 10, then =SUM(A1:A3) returns 15.
This is different from the + operator. If you write =A1+A2+A3 and A2 is text, Excel returns a #VALUE! error because the operator tries to coerce text to a number. So using SUM is the recommended way to total a range that may contain text.
Why Text Appears in Numeric Columns
Text often sneaks into columns that should be numeric. Common causes include:
- Data imported from other systems (e.g., CSV files) where numbers are stored as text with leading apostrophes.
- Cells formatted as text before entering numbers, which forces Excel to treat them as text.
- Invisible characters like spaces or non-breaking spaces from web copy-paste.
- Units or symbols attached, such as "$5" or "10 kg", which make the cell text.
When you try to sum such a column, SUM returns 0 because none of the cells are true numbers. This is a common frustration, but there are simple fixes.
How to Sum Numbers Stored as Text
If your cells look like numbers but SUM returns 0, the values are likely stored as text. Here are three reliable ways to handle them:
- Use the VALUE function with SUMPRODUCT: If your range is A1:A10, use
=SUMPRODUCT(VALUE(A1:A10)). This converts each text number to a real number and sums them. Note: this works only if every cell in the range can be converted; if any cell contains non-numeric text, it returns a#VALUE!error. - Use SUMIF with a large number criterion:
=SUMIF(A1:A10,"<9.99E+307")sums only cells that are less than the largest possible number in Excel, effectively ignoring text. This works even if some cells are text and some are numbers, and it ignores text that cannot be converted. - Convert text to numbers with Text to Columns: Select the range, go to Data > Text to Columns, choose Delimited, click Next twice, and on the last step choose General under Column data format, then click Finish. This converts text numbers to real numbers permanently.
For a quick one-off, you can also use the Error Checking button (the green triangle) that appears next to cells with numbers stored as text; click it and choose Convert to Number.
Using SUMPRODUCT to Filter and Sum
Beyond simple summing, SUMPRODUCT is a powerful tool when you need to sum numbers that meet a condition while ignoring text. For example, to sum only positive numbers in a range that contains text, use =SUMPRODUCT((A1:A10>0)*A1:A10). This works because the comparison (A1:A10>0) returns an array of TRUE/FALSE, and when multiplied by the range, text cells are treated as 0 (since TRUE*0 = 0, FALSE*0 = 0, and text coerced to 0). However, be cautious: if a cell contains text that cannot be coerced, the multiplication may produce a #VALUE! error. In practice, SUMPRODUCT handles text gracefully in this context because it coerces text to 0 in arithmetic operations.
Another common use is summing numbers that are stored as text but also have a numeric condition. For instance, =SUMPRODUCT((A1:A10<>"")*(VALUE(A1:A10)>5)) would sum values greater than 5 after converting text to numbers, but this requires all cells to be convertible.
Common Problems and Solutions
Here are frequent issues users encounter when summing with text present:
- SUM returns 0: This usually means all values are text. Use Text to Columns or the
VALUEapproach. - SUM ignores some numbers but not others: Check for hidden spaces. Use
=TRIM(A1)to remove spaces, or use=SUBSTITUTE(A1,CHAR(160),"")to remove non-breaking spaces. - SUM includes numbers that are formatted as text with a leading apostrophe: The apostrophe is invisible. Use Text to Columns or the Error Checking button.
- SUM returns #VALUE! when using SUMPRODUCT with VALUE: This happens if any cell contains non-numeric text. Use
SUMIFinstead, which ignores non-numeric text. - SUM ignores numbers that are actually dates: Dates are stored as numbers, so they will be included. If you want to exclude dates, use
SUMIFwith a criteria like">0"and"<1"for fractions, or check the cell format.
Alternatives to SUM for Mixed Data
Depending on your exact need, other functions can be more appropriate:
- SUMIF: Sums cells that meet a single condition. For example,
=SUMIF(A1:A10,">0")sums only positive numbers, ignoring text and negative values. - SUMIFS: Sums cells that meet multiple conditions. For example,
=SUMIFS(A1:A10,B1:B10,"Yes")sums numbers in A where the corresponding B cell is "Yes". - AGGREGATE: Function 9 (SUM) with option 6 (ignore errors) can sum while ignoring error values, but it still ignores text. Use
=AGGREGATE(9,6,A1:A10)to sum numbers while ignoring text and errors. - SUMPRODUCT: As shown, it can perform array-like calculations and handle text in certain ways.
Frequently Asked Questions
Q: Why does my SUM formula return 0 even though I see numbers in the cells?
A: The numbers are likely stored as text. Check for a green triangle in the cell corner, or use the ISTEXT function: =ISTEXT(A1) returns TRUE if A1 is text. Use Text to Columns or the Error Checking button to convert them.
Q: Can I make SUM ignore text without converting it?
A: Yes, SUM already ignores text by default. If you have numbers stored as text, SUM will ignore them too, which is why you get 0. To include those text numbers, you need to convert them or use SUMPRODUCT(VALUE(...)) or SUMIF with a large number.
Q: What if my range contains errors like #DIV/0!?
A: SUM ignores errors, but if you use SUMPRODUCT with VALUE, it will propagate the error. Use AGGREGATE(9,6,range) to sum while ignoring errors and text.
Q: Is there a way to sum only numbers that are greater than a certain value, ignoring text?
A: Yes, use SUMIF with a criteria like ">10" or SUMIFS for multiple conditions. Text cells are automatically ignored because they don't satisfy numeric comparisons.
Q: Does the SUM function ignore numbers formatted as text if I use the whole column reference?
A: No, it ignores them regardless of the reference size. If you use =SUM(A:A), it will sum all numbers in column A and ignore text, but it will not convert text numbers. To include text numbers in a whole column, you'd need to convert them first.
Mac and Excel for the Web Differences
The functions and behavior described above work identically in Excel for Microsoft 365 on Windows, Mac, and Excel for the web. The only difference is the location of the Text to Columns command: on Mac, it's under Data > Text to Columns as well, but the keyboard shortcut is Command+Option+E (on Windows it's Alt+A+E). The SUMPRODUCT and SUMIF functions work the same across platforms.
Related
- Excel Power Query Web Data: How to Import & Refresh
- Excel Power Query Weighted Average: How to Calculate
- Excel Power Query Undo: What Works and What Doesn’t
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Indent a New Line in an Excel Cell: Easy StepsTo indent just one new line in an Excel cell, edit the cell, insert a line break with…
- How to Add a Second Header in Word on Different PagesTo add a second line to a Word header, open the header area at the top of a…
- Conditional Formatting Based on Another Cell in ExcelYes. In Excel for Microsoft 365, conditional formatting can check the value in another cell and change the…
- Google Sheets Duplicate Table: Copy Data or a Whole SheetTo duplicate a table in Google Sheets, select its cells, copy them, and paste them into a new…
- Excel File Merge Online Free: Safe Ways to Combine WorkbooksYes, you can merge Excel files online for free, but the best method depends on what you mean…
- Excel Find and Replace: Change Cell Fill ColorYes. In Excel for Microsoft 365 on Windows, press Ctrl+H, open Options, choose a fill format beside Format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.