Results 1 to 9 of 9

20040422, 07:50 #1
 Join Date
 Sep 2003
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
SUM function don't work (Excel 2000)
Hello all, i got a strange document where the sum function don't work. When i type new numbers it's not a problem but i can sum the numbers already in the document....
document is attached
Regards Gerwin

20040422, 08:20 #2
 Join Date
 Oct 2002
 Location
 Wellington, Wellington, New Zealand
 Posts
 621
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM function don't work (Excel 2000)
They're not numbers (on my system)  they're right justified with thousand and decimal separators that are different from the ones I use.
I presume they can be fixed by replacing all "." with "" and then the "," by "."

20040422, 08:25 #3
 Join Date
 Sep 2003
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM function don't work (Excel 2000)
No, that's not it. I've i do format cells and than numbers it also not work. And the sum function is working with other documents with , and.

20040422, 09:08 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUM function don't work (Excel 2000)
When I do the replacements as suggested by Andrew the SUM works fine in the file.
SUM only sums numbers. The "numbers" are text that only look like numbers. Change them to numbers and use formatting to format the numbers with the thousand separators and decimal separators and the numbers will stay as numbers and the sum will work
Steve

20040422, 09:22 #5
 Join Date
 Sep 2003
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM function don't work (Excel 2000)
OK i've made numbers from it and save it agen and take a look. The sum of all this numbers is 0.00 that's not correct
see attachment

20040422, 09:44 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUM function don't work (Excel 2000)
They are not numbers.
See my attached with the numbers.
If you enter a "thousands" separator manually, excel will make them text. If you format to include it excel will add it.
Steve

20040422, 10:27 #7
 Join Date
 Sep 2003
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM function don't work (Excel 2000)
Sorry, it works correct!!! thanks allot! how can i see that it where fake "." ???
Regard gerwin

20040422, 10:43 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: SUM function don't work (Excel 2000)
If the cell contains a number, you will see the number stripped of its formatting in the formula bar, i.e. without thousands separator.
If the cell contains a text value that looks like a number, you will see the text "as is" in the formula bar, including the thousands separator.
You can also select a range of cells and set the horizontal alignment to General (in the Alignment tab of Format  Cells...). True numbers will be rightaligned, text values will be leftaligned.
There are several ways to convert text values that look like numbers to real numbers, for example:
 Select a series of cells in a single column. Select Data  Text to Columns... and click Finish.
 Select a blank cell and copy it to the clipboard (Ctrl+C). Select a range of cells (may be more than one column), then select Edit  Paste Special... Click the Add option, and click OK.
 Excel 2002 and up have smart tags to handle this.

20040422, 10:45 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUM function don't work (Excel 2000)
If you select a cell (or cells) you can see if they are "justified" (in this case the cells are "right justified"). If there is no "set justification" (you can "reselect" the justification to remove it), then text is left aligned and numbers are right aligned.
If you had removed the justification, you could see that the "numbers" were actually "text that looks like a number" since it would have left justification.
Steve