Ask Your Question

I am unable to get a correct sum in my calc document.

asked 2017-12-03 21:36:40 +0200

crayphish gravatar image

updated 2017-12-05 14:38:41 +0200

erAck gravatar image

I have formatted the column as USD currency. I have manually entered the formula =sum(g2:g22) and get zero. I have read as much as I can find and watched numerous videos and seem to be doing it correctly. I am not allowed to upload a file without having earned 3 points. :(

thanks, Kelly

edit retag flag offensive close merge delete


  1. You can always use free file sharing services to provide your file, like
  2. See this question.
Mike Kaganski gravatar imageMike Kaganski ( 2017-12-03 21:59:34 +0200 )edit

Please check first if the currency amounts are actually numbers. If you entered something recognised a text (though looking otherwise to your eye probably) it is ignored by SUM().
Try 'View' > 'Value Highlighting' to get different font colors for
Constant Text : Black
Constant Number : Dark Blue Returned by Formula : Green With default cell style also the standard alignment shows a difference
Text whether constant or calculated : Left
Numbers whether constant or calculated : Right

Lupp gravatar imageLupp ( 2017-12-03 22:06:46 +0200 )edit

I have highlighted and selected the columns and selected currency by selecting USA currency a few times. I used value highlighting to see the colors (very cool) and even though my selection is correct with currency populating when I select format, the data has not converted to currency. ??

crayphish gravatar imagecrayphish ( 2017-12-05 12:04:50 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-12-05 14:37:42 +0200

erAck gravatar image
edit flag offensive delete link more

answered 2017-12-05 13:18:40 +0200

Lupp gravatar image

Format settings do not convert anything.
To get entries taken in account by functions like SUM(), COUNT(), AVERAGE() they must be recognized as numbers.

The CONTENT of any cell is always one of the four types Empty, Number, Text, or Formula. The result of a formula can be of type Number or Text (or Error). Only content or result type Number is evaluated by the mentioned functions. (Errors are passed to the result.)

If you enter supposed amounts in a way "recognized" as type Text by the program this won't change if you change the 'Numbers' format of the respective cells later. You need an explicit conversion Text --> Number then.
If the entered texts are recognizeable with their intended meaning under the settings made, you can trigger a new recognition with the help of 'F & R'.
To advise how to do it in your specific case would require detaild knowledge about what you actually have: Contents as well as settings.
Regard the advice by @Mike Kagansky from the first comment on your question to supply a relevant sample MySample.ods.

edit flag offensive delete link more


Ah, this is an imported csv downloaded file that I saved into calc. I do not have a twitter account at this time to download with. Thank you for the explanation. It is very clear and I appreciate it. I will create a twitter account, hopefully today, to upload what I have.

crayphish gravatar imagecrayphish ( 2017-12-06 12:30:41 +0200 )edit

Hopefully you find a better way than twitter. There also should be serious upload services.
Did you study the answer by @erAck ? It should solve the problem anyway.

Lupp gravatar imageLupp ( 2017-12-06 12:35:47 +0200 )edit

Thank you all so much. @erAck's directions worked perfectly. :)

crayphish gravatar imagecrayphish ( 2017-12-09 13:03:37 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-03 21:36:40 +0200

Seen: 119 times

Last updated: Dec 05 '17