Numbers Spreadsheet not recognising values so formula SUM is not working

Hi Krishunt,

I think that Olivia has hit the nail on the head. The SUM function ignores text. Delete the pound symbol and the space. Use only numbers and let Numbers do the formatting for you. Here we go with Format > Cell > Data Format > Currency

Upvote to Olivia's reply 🙂.

Regards,

Ian.

Is it possible that the space between the pound symbol and the numbers is causing the issue?

Alternatively, you could create a new column and use =VALUE(your target cell) to extract the numbers first. Then, set the cell format of the new column to currency. This might help solve your issue.

A number can be one of those but not both. If 100,00 is a number then 100.00 is not, it will be text. The region settings on your computer determine which decimal separator to use. The region can be overrridden in File->Advanced->Language & Region but, regardless, only one of the two (point or comma) will be a number. If you are wondering why you cannot have both, take the example of 1,001 . If both separators were allowed at the same time, is this one and one thousandths or is it one thousand and one? Could be either.

Here are two ideas for importing new data that uses decimal points, based on your statement that you cut/paste from Excel (which is likely also treating it as text):

If Excel is not treating these numbers as text, you may be able to open/import the Excel file in Numbers then copy/paste from that Numbers document to your other document.

If that doesn't work (and I suspect it will not),

1. Create a new Numbers document to be used for importing the data that has decimal points.
2. Use File->Advanced-Language & Region to set the region of that file to one where the point is the separator (like US). First set the language then the region setting will become active and you can set the region.
3. Save this document for future use.
4. Copy/Paste from Excel into this "import" file. The numbers with decimals will become actual numbers/currency.
5. Copy/Paste from there to your other Numbers file. It will convert all the numbers to have commas and they will be numbers/currency.

Easy.

1. Change the region of your spreadsheet to one that uses the decimal point. All actual numbers (those that had commas) will convert to decimal point numbers. All the problematic ones that had been decimal points will stay as they are but will still be text (format will probably say "automatic" but they will be text).
2. Select and format all those text-formatted decimal point numbers as currency (or number)
3. Change the file back to System-English

One simple way to tell the difference between actual numbers and text-formatted numbers is by how they align in the cell, but this only works if you let Numbers do the text justification. If you set the text justification back to automatic (the icon on the far right) vs centered, text will justify to the left and actual numbers to the right.

Krishunt wrote:

Seems very frustrating that I just cannot reformat the cells. I don't understand why you have an option to reformat into currency but the computer won't allow it?

If the decimal separator is supposed to be a comma, a "number" such as 100.01 is not a number and will not be recognized as a number. Think of it as being in a different language. It first needs to be translated into the proper language by converting the period to a comma and removing any commas used as thousands separators (or replacing them with a space). Only then can it be recognized as a number. Here is a similar problem: use the word "桌" in an English sentence. You cannot, that character is meaningless in English. It has to be translated first (as "table")

Also, after the conversion you'll want to make sure that they all really do become numbers/currency and are not still text. SUM does not sum text, even if that text looks like a number. The +-*/ operators work on text-formatted numbers but none of the numeric functions do, they will skip right over them. I don't know the legacy but I tend to blame stupid stuff on Microsoft. If it does something dumb there then all compatible apps (like Numbers) have to do it, too.

If it reverts back to automatic, that means it is unable to set it to the format you tried because it does not match that data type. You said that some of your other sheets (tabs) work. In all those other sheets, are you using a point for the decimal separator (like in your screenshots here) or a comma?

Hello~ Our devoted and longtime volunteers in the Numbers forum are indeed brilliant. The fact that you have told them this and thank you holds you in very good stead with me. They work hard to help here and are often never thanked at all. Thank you for being appreciative to them all. Hang around in this forum and you will indeed learn quite a bit.

~Katana-San~