21

I'm stumped in Excel (version 16.0, Office 365). I have some cells that are formatted as Number, all with values > 0, but when I use the standard SUM() on them, it always shows a result of 0.0 instead of the correct sum. When I use + instead, the sum shows correctly.

For example:

  • SUM(A1:A2) shows 0.0
  • A1 + A2 shows 43.2

I don't see any errors or little arrows on any of the cells.

Improve this question
1
  • I could not produce this behavior only by entering data as text (with small backtick). In all other cases, both functions work the same. Commented May 16, 2020 at 14:06

17 Answers 17

Reset to default
21

Excel is telling you (in an obscure fashion) that the values in A1 and A2 are Text.

The SUM() function ignores text values and returns zero. A direct addition formula converts each value from text to number before adding them up.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, yes, using NUMBERVALUE() on every cell fixed it. I wish there was an easier way to set a cell type than having to use a function on every one. Some of these cell definitions can get very long and complex, and anything to reduce the length of complexity would be helpful.
It's confusing because in almost all the documentation I've found, they use "format" and "type" interchangeably. Most of the pages tell you tell you to set the type of a cell by changing its format. Like this: tutorialspoint.com/excel/excel_setting_cell_type.htm
Just had this problem for a working excel sheet... moved rows, all cells having type "Currency" but SUM() is not able to sum.... changing to "Number" did not help - removed values and entering again + using a+b and later sum solved it... (for me it looks like a bug in Excel Web, using Excel Web)
Another source of the problem is the "Fixed" worksheet Function. It returns TEXT. Useful for presentation/display, but cells later referencing cells containing "Fixed" may not Sum correctly. Wrapping in NumberValue will indeed avoid that symptom.
However, if you tell Excel that a column contains text and not numbers (e.g. there is a name "0040" that you want used as text, not the number 40) the sunburst chart completely ignores the cell type and treats "0040" as the number 40. The only way around it is to stuff the cell with a non-whitespace non-numeric character and live with it.
15

I get a similar issue while importing from a csv.
Selecting the cell range and formatting as number did not help
Selected the cell range then under:
Data -> Data Tools -> Text to Columns -> next -> next -> finish
did the job and numbers are now turned into numbers that excel consider as numbers !
This avoids use of NUMBERVALUE()

Improve this answer

1 Comment

The above method worked like a cham!; Thanks
11

Using NUMBERVALUE() on each cell fixed it. Even though each cell was formatted as a Number, since the data was originally extracted from text, the cell contents apparently were NOT being treated as a Number. Yet another flaw in Excel.

Improve this answer

6 Comments

Are you using any formulas to extract the numbers?
"Yet another flaw in Excel" - not exactly. Formatting, and the underlying value, are not the same. If you want a number, you actually have to convert it to a number... (e.g., nothing happens if you apply the Number format to a cell containing the text "foo", and similarly, nothing happens if you apply number formatting to a cell containing the text-that-looks-like-a-number "1").
@BigBen - your comment is helpful. I know how to enter 'text that looks like a number' - by putting a single apostrophe (') at the start of the cell. But I've just downloaded a spreadsheet from my verizon account, and all the 'numbers' are flagged with a small green triangle upper-left, and a warning 'the number in this cell is formatted as text or preceded by an apostrophe' - but - I see no apostrophe (even in edit-mode), and I changed the format to 'number' but it made no difference. BUT - the 'warning' has the option to 'Convert to number' - and that fixes it! But I can't do it manually.
@Steerpike - not all text-that-looks-like-a-number is preceded by an apostrophe. For example, format a new cell to text, then type any number in it.
@Steerpike - there are definitely other ways to convert in place. For example, put 1 in a new cell, copy the new cell, then select the text-that-looks-like-a-number, paste special > multiply.
|
1

I noticed that if the sum contains formulas if you have any issue with circular references they won't work, go to Formulas-->Error checking-->Circular References and fix them

Improve this answer

Comments

1

There is a much faster way you just need to replace all the commas by points. Do control-F, go to "Replace" tab, in "Find what" put "," and in "Replace with" field put "."

Improve this answer

1 Comment

Can you explain your answer? I'm not sure what commas are you talking about
1

You should type in a row: =SUM(VALUE(A1:A2)) and then press ctrl+shift+enter

Maybe it works.

Improve this answer

Comments

0

If there's a tab in your .csv then Excel will interpret it as text rather than as white space. A number next to a tab will then be seen as text and not as a number. Convert your tabs to spaces with a text editor before letting Excel at it.

Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
0

I've just solved the problem by replacing all Number's "." with "."

How the heck can this be real? Feel really misunderstood by Excel....

Improve this answer

3 Comments

Are you saying that you just replaced all dots with dots? Or is there a typo in your answer?
Yes exactly, no typo there. It was very weird but worked for me 😅
Worked for me too. A SUM() of a range was showing 0.0 as a result. Changed dots to dots as suggested, and the correct sum was shown.
0

Numbers showing up as numbers when they are actually texts. Using the ERROR sign next to the cell to convert the "text" into a numbers looks like the only uncomplicated solution that works. Hectic when working with huge amounts of data. a bug in excel<<<

Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
0

For me, in excel2007, since a column contains mixed value number with strings, I needed doing some steps:

  1. I Removed "," on colonn data source (column A) with "Replace" Excel option.
  2. I Created a column without all text in my case strings are 6 last characters. I removed with this formula: =left(E2; NBCAR(E2)-6).
  3. I created a column C to transform possible string to number with function formula.
  4. If "#VALUE!" is showed on "Sum" cell, check last cells have a string.
A2  Remove ","          -->  B2 Remove text with formula --> C2 Show value with "cnum"
1,894,529.000000BTC|         =left(A2; NBCAR(A2)-6)          =cnum(b2)

Good sum's ;)

Improve this answer

Comments

0

I think I may have solved the answer to this question once and for all, well for Excel users anyway.

OK, bear with me, this is a lot easier than it might sound. After getting a trading account CSV from my bank on a regular basis I ran into this problem over and over. No amount of formatting inside excel worked.

So please note -> This has everything to do with HOW you import the file.

So, with the CSV downloaded on your machine or accessible via a dialog box do the following.

  1. Open Excel with a blank sheet, do NOT click on the CSV first to then open Excel, this will not work that way.
  2. Then select DATA button from the menu and then GET DATA (Power Query).
  3. Then select the TEXT/CSV button.
  4. Then click browse and select your CSV and then click GET DATA and then NEXT.
  5. Check the delimiter based on your specific file (you can see how the transformation has worked in the window), in many instances this will be a comma, yours may differ.
  6. If you think there is something fishy with your file formatting then you can use the TRANSFORM DATA to deal with other oddities you may see, otherwise just click LOAD.

Now, when you are in your imported sheet you should see the correctly formatted numbers in the relevant columns. Go ahead and try the sum() function under your column. You will also see that the data is "seen" by the Average and Sum function on the bottom status bar, which was not the case before.

Hopefully this has helped at least one person. This stumped me for HOURS and so when I found what worked for me (can't truly promise it works for everyone), I had to share. And NO, I don't know how you would do this in Numbers.

All the best guys 'n' gals. Happy importing.

Improve this answer

Comments

0

This is probably not the most logical fix, but I selected the numbers I would be trying to add/sum, hard-code those numbers (copy, paste as values), and then Ctrl+Z - the sum function would fix itself this way.

Improve this answer

Comments

0

OK, select the column of cells you are trying to sum. Select -Replace- then "find what" and type the currency symbol (in my case) £ in the 'Replace with' leave empty. then press button 'replace all'

With the column of cells still selected insert the currency symbol from the drop down menu and it will be ok. Essentially even though it shows a currency symbol it's actually text and excel is not recognising the symbol so you have to delete the old one and insert a new one from the correct menu. This is the problem I had when importing from a csv file - solved it straight away.

Improve this answer

Comments

0

I had a case of SUM() failing on formula cells that outputted a number, but using IMSUM() (for adding complex numbers) worked instead.

Not sure how consistently it would work, but might be easier than using NUMBERVALUE() on every cell like other suggestions. Bizarre.

Improve this answer

Comments

0

Try removing the space inside the cell. When there is additional spaces in the cells you are selecting, Excel will treat them as text instead of Numbers. Worked for me.

Improve this answer

2 Comments

The "Text to Columns" method you mentioned is effective. Plus, instead of just saying "removing the space," provide a clearer and more comprehensive solution.
Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
-1

My lazy fix is to just create a new column, making sure the first cell has no text in it to prevent all cells being cloned to this type. They should all be General rows. Copy the formula from the first cell and adjust any relative cell references to the new column; or just right click Paste Special Value -->Values Only. Copy the changed formula down and the SUM function should then miraculously spring to life

Improve this answer

1 Comment

If you create a new column there is for sure no text in it.
-1

If any of the cell in range have formula then SUM will show 0.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.