Ask Your Question

Auto sum equals 0

asked 2019-04-20 13:39:01 +0200 gravatar image

I've read questions and answers to the auto sum issue. I downloaded an excel file to libre and cannot get the auto sum to work. each value has an " ' " in front as text. I tried editing the cell format from text to number or accounting and it doesn't change it. This is very frustrating to say the least. Please help

edit retag flag offensive close merge delete


BigRAl gravatar imageBigRAl ( 2019-04-20 14:11:15 +0200 )edit

Thank you BigRAI, that worked. In the meantime, I actually tried Google Docs. Opened the spreadsheet in Google Docs, then used the Format Cell option. This worked. But I don't want to have to go thru this every time I use an Excel file. Even the method within LibreOffice is somewhat cumbersome for a simple AutoSum calculation, but is workable. And fortunately, I don't have to use AutoSum frequently. Thanks again for your quick reply to this issue. gravatar ( 2019-04-20 15:21:34 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-04-20 15:37:26 +0200 gravatar image

Thank you Opaque, that worked. So basically with your help/reply and BIGRAI's above, that is 2 options to convert "text" numbers to actual numbers. And you are correct that in both cases, the values in the cells immediately aligned to the right side of the cell. I noticed another thing that when you do first try to "autosum" the cells, it does not activate the cells above. Makes sense since at that point they are not identified as "numbers" but text. Thank you for your quick reply and help. My frustration has ended !!!

edit flag offensive delete link more


Hello - great that it works for you, but please do use "add a comment", if you want to comment an answer instead of using "Post Your Answer". Finally please mark an correct answer, which solved your problem as correct by clicking the check mark ✓ next to the answer.

Opaque gravatar imageOpaque ( 2019-04-20 16:40:40 +0200 )edit

answered 2019-04-20 15:15:06 +0200

Opaque gravatar image


changing the format of a cell does not change the text property of existing content, it stays to be text. Thus you need the text to be changed to numbers and the easiest was is:

  • Select all cells with the text which should be numbers
  • Go to Data -> Text to columns and you'll get the following dialog (Click the square as indicated)

    image description

  • The dialog changes to the following after you've clicked the square (Select "Standard" as indicated and click "OK"

    image description

Now your "text numbers" should be real "numbers" and "SUM" should work (BTW: If you did not change/set any alignment the left alignement [default for "text"] changes to right alignment [default for "numbers"] immediately after the change has been performed)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-20 13:39:01 +0200

Seen: 31 times

Last updated: Apr 20