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

      • davidmgahan's avatar
        davidmgahan
        Copper Contributor
        Oct 09, 2024
        Hi Terryloo,
        Thank you for your help.
        I tried that but to no avail.
        Can you confirm I should save the file as a type "Text (tab delimited .txt" file? or can you tell me the extension I should see in the save as options?
        When I saved it as as .txt file, there were only ? in from of the numbers but no " symbols and when I did the step 'replace with nothing' all the data disappeared from the sheet.

        Thanks again for helping.
        David
    • davidmgahan's avatar
      davidmgahan
      Copper Contributor
      Oct 09, 2024

      HansVogelaar Thank you for your reply.

       

      I've tried that but I am messing up somehwere, I think.

       

      I am unsure how to do one step in your instructions:

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

      Can you tell me how to do that?

      (I've tried pasting a non-breaking hyphen symbol into a blank cell and then copy / pasting that into the 'find what' box and continued to the last step in your instructions (replace with a standard minus in the 'replace with' box but that doesn't seem to work for me).

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 09, 2024

        davidmgahan 

        You can select and copy the minus sign in one of the cells, then paste it into the Replace dialog.