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
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
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. ??
Format settings do not convert anything.
To get entries taken in account by functions like SUM(), COUNT(), AVERAGE() they must be recognized as numbers.
Explanation:
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 @Mike2 Kagansky from the first comment on your question to supply a relevant sample MySample.ods.
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.
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.
I had the same problelm when I first used CALC. It is buggy. It is not an issue of importing numbers. I formatted the columns as numbers before I typed them in manually with the same issueâŚ
To trick the software into correctly performing a SUM, just use the =VALUE() on one of your numeric fields, verifying that it is indeed a number, and then the math functions will work - hopefully, at leastâŚ
I suspect this to be bad advice based on serious misunderstandings.
SUM() works correctly if correctly applied. It is specified explicitly to ignore any text passed via the arguments, whether it looks like a number or otherwise (or is empty).
Additional functions ignoring text entries completely are COUNT() and AVERAGE().
Thus: AVERAGE(arguments) = SUM(arguments) / COUNT(arguments).
(There must have occurred an error with your formatting. Related suggestion: Never define explicit horizontal alignment for columns, and make them a bit wider than âoptimalâ. you will then clearly see where actual numbers are displayed, and where texts probably looking like numbers: Only the texts are left aligned. This holds if a cell formatted to an actuall number format got entered -for what reason ever- a number with a prefixed apostrophe.)
What I am saying is, that the problem occurs even when the numbers were typed in fields originally formatted as numbers. As proof, you can try â=VALUE()â and input one of the fields and determine that the system indeed recognizes the field as a number. The issue has nothing to do with entering text values (although that is a plausible explanation).
That is my experience with it. I diid not change the values, but once I invoked the â=VALUE()â function on one field, it always operated correctly.
I actually got this suggestion from someone on another thread.
I actually read your comment. Did you also read mine?
The function VALUE() is specified to return a numeric value for a text which can be recognized as a number and for a content or formula-result already being a number as well.
(There actually are a some inconsistencies concerning the fake number-format with code @, which in fact is a directive concerning the interpretation of an input⌠Itâs a mess like so many âsmart featuresâ. But thatâs not the reason of what you claim to be a bug.)
demoSumNoTexts.ods
ok, wow, thatâs a very interesting spreadsheet.
And I do acknowledge my misunderstanding of the VALUE() function.
And I consent that hoizontal alignment can obscure whether the entries are either numbers or text.
This does not change my conclusion, however.
Can you explain how (using the same columns and entries) SUM(D1+D2+D3) would result in a valid computation, whereas SUM(D1:D3) would result in 0?
Your mentioning a âfake number formatâ is an interesting twist, though.
D1+D2+D3 is an expression which is not ruled by the specification of the SUM() function. That SUM ignores text is only relevant for each parameter position (or the elements containd in ranges placed there). On the other hand ordinary addition (operand +) is specified to return a number, and to try automatic type conversion of operands being of string type if possible.
Thus "2" + 5 + "9" results in 16 (number), and this number on a parameter position of SUM() is accepted, of course. The same with "2" + "5" + "9".
On the other hjand the operand & is specified to return a string, and will convert numbers given as operands to strings (using a default format). Thus 5 & 7 results in the string 57, and SUM(5 & 7) will therefore return 0.
Dissect expressions top down, and be aware of the fact that they are evaluated bottom up.
And:
-1- Avoid automatic conversions where in any way doubtable.
-2- Simply write =D1+D2+D3 if you mean it. SUM() obsolete.
Thank you for your passion, and your knowledge, and you patience in responding. You have all my respectâŚ
