Sum returning 0 in Excel

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:

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

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

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

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.

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

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

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