Sum returning 0 in Excel

Solution 1:

To remove double quotes, select the cells to convert, and use Find->Replace to change quote (") to nothing.

If the cells remain as text, here's a method that will convert text to numbers using the Paste Special command.

In any blank cell, type the value 1
Make sure the cell in which you typed 1 is formatted as a number
Select the cell with the 1 and Copy
Select the cells with the values you want to convert to numbers
Choose Paste Special
Under Operation, click Multiply and then click OK

Solution 2:

The 'numbers' you are trying to add are Texts, not numbers.

That happens when they get loaded from sources that mark them accordingly, or when the format of the cell is set incorrectly.

Unfortunately, there is no simple two-click way to fix it - changing the format of the cells is not going to change the content accordingly, you would need to re-enter each value. There are several ways to fix it, though:

If you loaded the data from a CSV or other non-Excel source, the easiest is to repeat that, and mark the column as 'Number' instead of 'Text'. To do this, open a blank Excel sheet, go to Data/From Text, and follow the wizard. In step 3/3, make sure to click on the column and select 'General', not 'Text'
if this is not an option, you can use a helper column (like right next to the values), and enter =VALUE(E1) into F1, and copy it down. This column will now be summable. You can also then copy the content of column F and 'Paste/Values only' over column E, and then delete the helper column.
third option is to combine this in an Array-formula: instead of =SUM(E10:E13) use =SUM(VALUE(E10:E13)) and press CTRLSHIFTENTER (instead of only ENTER).

Solution 3:

I also use an Excel with a French locale, and often face this problem with CSV files. As explained by others, the French numbers use a coma as a decimal separator, and a number like 123.45 is interpreted as a text by Excel.

The fastest way to circumvent this, is to replace . with ,. You can do it quickly with Find/Replace, as suggested by David.

For those who face this problem quite often, you can use this piece of code (adapted from this answer), save it in your PERSONAL workbook and assign it to a button in the ribbon:

Sub Comas2Dots()
    Application.ScreenUpdating = False
    If (MsgBox("Do you want to replace comas by dots?", vbOKCancel) = vbOK) Then
        Const sTEMPCOMMA = "|comma|"
        Const sTEMPDOT = "|dot|"
        Const sCOMMA = ","
        Const sDOT = "."

        If TypeName(Selection) = "Range" Then
            With Selection
                .Replace sCOMMA, sTEMPCOMMA, xlPart
                .Replace sDOT, sTEMPDOT, xlPart
                .Replace sTEMPCOMMA, sDOT, xlPart
                .Replace sTEMPDOT, sCOMMA, xlPart
            End With
        End If
    End If
    Application.ScreenUpdating = True
End Sub

Hope that helps people experiencing this unnecessary problem!

Additional documentation

How to assign macros to the ribbon
Copy your macros to a Personal Macro Workbook

Solution 4:

The cells you are trying to add are not seen as numbers. This is because there is a full stop "." and not a comma "," to represent the decimal part of the number.

A much faster way of fixing this is to use the find function (ctrl F), find all "." and replace with ",".

Solution 5:

Highlight the column that you are attempting to SUM, right click and Format Cells.
Select Number but remember to set decimal places to 0 if it doesn't apply to the value. Now although you have changed the format of the column, doesn't mean that the column has officially converted all of the values to numbers so do the following to resolve this
Highlight the entire column again, and click the Data Tab, Then "Text To Columns"

enter image description here

Select Delimited, click Next
Uncheck Tab and just click next
Click Finish

Sum should now reflect a number!