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
HansVogelaar's avatar
HansVogelaar
MVP
Oct 09, 2024

davidmgahan 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

davidmgahan's avatar
davidmgahan
Copper Contributor
Oct 09, 2024

HansVogelaar Thank you for your help. Link to sheet below. I'm simply trying to sum one column.

 

https://www.dropbox.com/scl/fi/84rm3t14qzhc2jdntwzcn/SHEET-WON-T-SUM.xlsx?rlkey=9i16ssxf12ilrim0e4g70lc0b&st=1jouiyey&dl=0

 

 

 

  • davidmgahan's avatar
    davidmgahan
    Copper Contributor
    Oct 09, 2024
    Sorry that link may not allow editing. Lnked again now.

    https://www.dropbox.com/scl/fi/84rm3t14qzhc2jdntwzcn/SHEET-WON-T-SUM.xlsx?rlkey=9i16ssxf12ilrim0e4g70lc0b&st=dhrrlx4h&dl=0
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 09, 2024

      davidmgahan 

      Thanks. The values in column D are text values although they look like currency amounts.

      Select the values.

      Press Ctrl+H to activate the Replace dialog.

      Enter € in the 'Find what' box and leave the 'Replace with' box empty.

      Click 'Replace All'.

      Next, clear the 'Find what' box and paste ‑ into it (warning: this is not the standard minus sign, it is a non-breaking hyphen!)

      Enter a standard - in the 'Replace with' box.

      Click 'Replace All'.

      The numbers should now sum correctly.

      • terryloo's avatar
        terryloo
        Copper Contributor
        Oct 09, 2024

        @davidmgahan
        Hello David, 
        I suggest you use the method I mentioned earlier. 
        Open your file on excel desktop.
        Save your file as a tabulation separated txt.

        You will see that your file has the numbers saved with those odd little symbols.
        CTRL + A

        CTRL + H
        Find " replace with (nothing)

        Find ? replace with (nothing)

         

        Once it's done, do the following:
        CTRL + A
        Copy 

        Open Excel 
        Paste as values.
        It worked for me