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

eagle1981 Similar or the same? This is an old thread and deals with a classic problem where users think that formatting a cell as a number doesn't change the content to a number if it is a text.

 

check the cells you are summing with ISNUMBER(cell_ref). Does it return TRUE? If not, you are dealing with texts that look like numbers.

KaitKilpatrick's avatar
KaitKilpatrick
Copper Contributor
Apr 05, 2023
How do you fix this in modern excel? The text to columns "wizard" isn't a thing for me... It just has options to select "tab," "semicolon," "comma,", "space,", or "custom." I have this data coming into my excel workbook from an MS form, and although it's set as "number" on the form, and the column type in my workbook, when I do the ISNUMBER(cell ref) formula, I am getting "false." Can someone please help?
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 05, 2023

    KaitKilpatrick 

    As variant

    You may copy any empty cell (Ctrl+C)

    Select column with numbers as texts

    Paste->Paste special->Add->Ok

     

     

    • philip369's avatar
      philip369
      Copper Contributor
      Apr 27, 2023

      I understand conceptually the difference between text and numbers but it is so incredibly frustrating that EVERY time I open a new document and manually enter numbers (not cutting and pasting) I cannot get Excel to sum.

      Here's a silly example from today- new sheet, manually entered. This time I switched to commas to test whether it was an issue of being in France with a US version of Office 365 (how to switch this back to US/US format?).

      Now it "sums" on the bottom right and gives me an average but instead of a summed number it gives me

      I understand conceptually the difference between text and numbers but it is so incredibly frustrating that EVERY time I open a new document and manually enter numbers (not cutting and pasting) I cannot get Excel to sum.

      Here's a silly example from today- new sheet, manually entered. This time I switched to commas to test whether it was an issue of being in France with a US version of Office 365 (how to switch this back to US/US format?).

      Now it "sums" on the bottom right and gives me an average but instead of a summed number it gives me =SUM(A1:A5) AAARGH!! 

      What do I need to change here? Microsoft makes this so incredibly difficult!

      Thanks.

      AAARGH!! What do I need to change here? Microsoft makes this so incredibly difficult!

      Thanks.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 27, 2023

        philip369 

        Activate the Formulas tab of the ribbon and look at the Formula Auditing group.

        You'll notice that the 'Show Formulas' button is highlighted.

        Click this button to turn it off. You should now see the result of the formula.

         

Resources