Excel SUM function is not working (shows 0), but using Addition (+) works

Question

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.

Wafture

I could not produce this behavior only by entering data as text (with small backtick). In all other cases, both functions work the same. – Marotta

Answer 1

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.

Lynlyncean

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. – Wafture

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 – Wafture

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) – Spireme

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. – Ardys

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. – Eous

Answer 2

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.

Wafture

Are you using any formulas to extract the numbers? – Marotta

"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"). – Akkadian

@Akkadian - 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. – Tergiversate

@Tergiversate - 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. – Akkadian

@Akkadian - agreed; format new cell as text, type number in it - the 'number' typed is actually 'text'. But then, change cell format back to 'number', and that 'text' content remains (any FUTURE entry in that cell will be 'number' but the information you entered remains as text). Only way I found to convert EXISTING text content is to use 'Data/text-to-columns' - that will convert in-place. You'd think, in the 'format' dialog, there'd be a check box for 'convert existing content' or similar. – Tergiversate

@Tergiversate - 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. – Akkadian

Answer 3

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()

Dad

The above method worked like a cham!; Thanks – Bludge

Answer 4

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 "."

Helban

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

Answer 5

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

Gonidium

Answer 6

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.

Anaphora

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. – University

Answer 7

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

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

Puckery

Are you saying that you just replaced all dots with dots? Or is there a typo in your answer? – Garey

Yes exactly, no typo there. It was very weird but worked for me 😅 – Puckery

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. – Darlinedarling

Answer 8

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<<<

Oliviero

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. – University

Answer 9

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 ;)

Stomatology

Answer 10

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.

Precondemn