Articles

Why does SUM return 0 when numbers are stored as text in Excel?

The cells look like numbers. Excel reads them as words, adds nothing, and reports no error.

SUM, SUMIFS and SUMPRODUCT skip any cell that holds text, including text that looks like a number, and they do it without an error. A column of six areas exported as "1,200 sqm" adds up to 0. The fix is a formula that removes the unit and the separators and converts what is left: on this example the six cells then add up to 11,350 sqm. The first step is to detect the problem, because a total of 0 is the lucky case. A column where only some cells are text returns a total that is too low and looks plausible.

The case comes from the AI in Excel for Analysts course by Julian R. Sterling, author of The Real Estate Asset Manager.Get the DRM-free PDF, $19.99 →

What the export looks like

Six rows of a second export (fictional): the raw cells and what they should be.
Raw area (text)Area (number)Raw expiry (text)YearMonth
"1,200 sqm"1,200"30/06/2029"20296
" 850 sqm"850"30/09/2027"20279
"400 sqm "400"31/03/2031"20313
"2 100 sqm"2,100"31/12/2030"203012
"6,500 sqm"6,500"31/12/2032"203212
"300 sqm"300"30/09/2029"20299
SUM returns 011,350

Four different problems sit in the first column: a unit after the number, a comma as thousands separator, a space as thousands separator, and spaces before or after.

Detect before you fix

One formula to clean the areas

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," sqm",""),",","")," ",""))

Read it from the inside: TRIM removes the spaces around the text, the first SUBSTITUTE removes the unit, the second the commas, the third the remaining spaces, and VALUE converts the result into a number. Applied to the six rows, it returns 1,200, 850, 400, 2,100, 6,500 and 300.

If one cell still fails, the space is probably a non-breaking space, common in exports from web systems: add SUBSTITUTE(...,CHAR(160),"").

Dates that are text

"30/06/2029" is a date on a machine set to day/month/year and plain text on one set to month/day/year, because there is no month 30. DATEVALUE depends on the same setting. Build the date from its parts, which works everywhere:

=DATE(VALUE(RIGHT(B2,4)),VALUE(MID(B2,4,2)),VALUE(LEFT(B2,2)))

The worst case is a date that converts without error to the wrong day: 03/04/2029 is 3 April on one machine and 4 March on another.

Prove the result

Keep the raw column, put the cleaned values next to it, and add two checks: the count of numeric cells equals the number of rows (6), and the total equals a figure from another source, here 11,350 sqm. Then point every formula at the cleaned column, never at the raw one.

Asking an assistant for this formula

Paste three or four raw cells exactly as they are, between quotation marks so that the spaces show, and say what each should become. Give your Excel version and your regional settings: the decimal separator and the date order decide which formula is right. Then test the answer on every pattern of the column, not only on the first row.

Sources

  1. Julian R. Sterling, AI in Excel for Analysts (video course, 2026): running case Brackenridge portfolio
  2. Microsoft Support, function reference for SUMPRODUCT, SUMIFS, COUNTIFS, VALUE, SUBSTITUTE, TRIM and DATE
  3. ICAEW, Twenty principles for good spreadsheet practice

Every figure is computed by script from the course's running case, the Brackenridge portfolio, a fictional rent roll of 20 units, and checked on 19 November 2026. Function availability depends on your version of Excel. For education, not investment advice.

Formulas you can prove, step by step

26 short video lessons on using an AI assistant in Excel without being wrong: the five-part brief, SUMIFS, SUMPRODUCT, INDEX and MATCH, a mini sample with a known answer, checks that tie and the audit of a received model. You build a checked summary from a rent roll, with step files, exercises, a prompt kit and a certificate exam ($149).

Watch module 1 free →

Questions readers ask

Why does SUM return 0 in Excel?

Usually because the cells hold text that looks like numbers. SUM ignores text without returning an error. Test with ISNUMBER, or compare COUNT with COUNTA on the same range.

How do I convert text to numbers in Excel with a formula?

Remove what is not part of the number with TRIM and SUBSTITUTE, then wrap the result in VALUE. For a cell such as 1,200 sqm: remove the unit, the comma and the spaces, then convert.

Why does SUMIFS return 0 when the criteria are right?

Either the sum range holds numbers stored as text, or the criteria range holds values with extra spaces that do not match the criterion. Check the sum range with ISNUMBER and the keys with TRIM.

Read the whole case

This article is one calculation from The Real Estate Asset Manager. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.

See the course →Free companion files

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada

Prefer a PDF? The same book is sold as a DRM-free PDF edition, searchable and printable. Get the PDF edition, $19.99

Also on this site

All 2221 articles →