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.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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

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

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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.

Answer 13

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.