15

I'm working on an Excel spreadsheet and when I use a SUM formula in a cell and then I select columns, it returns 0. I think that it's due to the double quotes in the cell. I tried to format the cells but it still returns 0.

I'm using Microsoft Office 2010

Screen shot of the result

7 Answers 7

6

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
1
  • The problem doesn't come from the quotes. The quotes shown in the screenshot are here because the values are texts, not numbers, and the value doesn't contain any quote. The reason is that in a French locale, , is the decimal separator, not .. So values like 123.45 are texts, and can't be summed up. Multiplying by 1 like you suggest wouldn't work either in this case, because this is not a "numbers stored as text" situation, but rather a "text stored as text" situation. If you replace dots with comas, Excel will convert the texts into real numbers (this only applies to the French locale).
    – piko
    May 29, 2018 at 11:01
10

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

6
  • I tried with 2 and 3 solution but the =SUM(VALUE(E10:E13)) return ERROR so no solution. But accualy the file is formated csv , is there a way to convert it to .xlsx and mark the column as Number. Thx again Jul 30, 2016 at 16:43
  • @fixer1234 To me it seems it was suggested in the answer.
    – techraf
    Jul 30, 2016 at 16:57
  • @techraf, yeah. I was trying to address the OP's comment, but my comment just added confusion.
    – fixer1234
    Jul 30, 2016 at 17:07
  • @techraf and @Aganju I even applied VALUE function on each cell but it still return error =SUM(VALUE(E10):VALUE(E13)) Jul 30, 2016 at 17:17
  • @HaithemRihane, that cannot work, it is not an Array formula that way. Did you follow the instruction with the CTRL+SHIFT+RET? The result will display as ={SUM(VALUE(E10:E13))} even though you never typed the {}. I have also added the detailed instructions for 1.
    – Aganju
    Jul 30, 2016 at 17:30
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

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

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

1
  • @fixer1234 actually, David's right. In the French locale, which I also have, comma is the decimal separator. Period isn't used at all for numbers. The OP's problem is simply that the numbers are actually text, and to fix it, you need to replace . with ,. Of course, it only applies in the French (& similar) locale.
    – piko
    May 29, 2018 at 10:42
1
  1. Highlight the column that you are attempting to SUM, right click and Format Cells.

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

  3. Highlight the entire column again, and click the Data Tab, Then "Text To Columns"

enter image description here

  1. Select Delimited, click Next

  2. Uncheck Tab and just click next enter image description here

  3. Click Finish

Sum should now reflect a number!

-1

Open a new Excell sheet. format sheet as numbers, remove decimals if you don't need them. then copy your original sheet and special paste it as values only into the sheet you format as numbers only. Should solve ur issue u may need to format some sheets for datesting or whatever else u need and do all over your boxes if you had separated them but your formulas should work stupid thing made me waste half day figuring out its problem

-1

Make sure you don't have any rows that are hidden. I had two rows that were hidden and after I realized this and removed those rows, my calculation finally worked.

Not the answer you're looking for? Browse other questions tagged or ask your own question.