Forum Discussion

philip369's avatar
philip369
Copper Contributor
Dec 05, 2019

Impossible to sum numbers in Excel

I hope someone can help with this because I am pulling my hair out.  When I try to sum numbers in a column, at the bottom of the page it gives me a count and not a sum.  When I try to Autosum, it g...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Dec 05, 2019

philip369 

 

Understand! But if you format a text as a NUMBER it will still be a text and the SUM function will not work. Say you have "  10" in A1. This a is a text. Try entering the formula =VALUE(A1) to make the text that looks like a number into a real number. Otherwise, upload you workbook so that I can have a look at it.

 

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 05, 2019

    philip369 

    Went into your sheet and when I select each cell in column C and press enter in the formula bar, the left justified text becomes a number. Also added a formula in column D to demonstrate what I wrote in my previous message. 

     

     

     

     

    • philip369's avatar
      philip369
      Copper Contributor
      Dec 05, 2019

      Riny_van_Eekelen 

       

      Thank you! Is there any way to make this a default? 
      I've had many versions of Excel and this is the first time I have to go through all these steps for it to recognize text as a number (!)... Isn't that the point of Excel? 🙂 

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Dec 05, 2019

        philip369 

        Depends where your "numbers" come from. Importing them could cause them to be text. Just entering them them from scratch should, by default, recognise them as numbers.

    • Floris_GS1_NL's avatar
      Floris_GS1_NL
      Copper Contributor
      Dec 22, 2021
      I have tried this many times, but in my case nothing happens with the format. it stays as it is. The data comes from a table copied from Microsoft OneNote and the column hold only number values.(no decimals, comma's, spaces or other characters). It just won't do a thing!
      • Floris_GS1_NL's avatar
        Floris_GS1_NL
        Copper Contributor
        Dec 22, 2021

        Although the 'convert to table' still does not work for me, I found a way without having to F2 every cell as @Ringy_van_Eekelen mentioned.
        As I said there were no spaces in the cells as I had already done CTRL+SPACE + CTRL+H type 'space' in search field and hit replace all on the specific column, but no spaces were found.
        It turned out that what looked like a space in the cell behind the number, was probably something else. So I selected that white space from one cell behind the number, CTRL+C and CTRL+V in the CTRL+H search field and then hit replace all. Then it found 1001 replacements.
        Then I had to copy the whole column to Notepad, create a new column in Excel, set the format to Currency and paste the data from Notepad back in the new column. Now Excel see the data as numbers(currency) and can make calculation on it.