Excel does not perform sum totals on numeric values

[Emil Furniga]

Hello,

I have a colleague working with Excel 2000 (it's the Romanian version), and
from time to time she is experiencing this issue:

She is entering numeric values into a column and after that she is trying to
sum all the values in the column (the very well-known SUM formula). Weird is
that the sum does not work on the column, even if she tries all the tricks
in the book, moreover I tried everything I could think of ubt to no effect.
In concrete, I tried changing the format of the fields to numeric, tried to
copy the values to a different location (only values, no formats at all),
but the sum formula does not do the total nevertheless.

Curiously enough, I tried entering all the values again (in a parallel
column), and the total is working this time -- in fact, this is the only
solution I could find until now, however inconvenient this is.

Do you know a solution to this issue I've described ? And maybe a
description of the cause.

Thank you,
Emil.

 

[Pete_UK]

The column was probably pre-formatted as Text, so when she put the
numbers in they were really Text values that looked like numbers. When
you re-format the cell it does not immediately change the format - you
have to re-enter the values.

One way around it is to ensure that the cells in the column are
formatted as General, then click on an empty cell where the format is
also set to General, then click <copy>. Highlight all the offending
cells and click on Edit | Paste Special | Add (check) | OK then <Esc>.
This effectively adds zero to the numbers, but at the same time will
change the Text values to proper numbers. Your SUM formula should now
work.

Hope this helps.

Pete

 

[Chris J Dixon]

I have a colleague working with Excel 2000 (it's the Romanian version), and
from time to time she is experiencing this issue:

She is entering numeric values into a column and after that she is trying to
sum all the values in the column (the very well-known SUM formula). Weird is
that the sum does not work on the column, even if she tries all the tricks
in the book, moreover I tried everything I could think of ubt to no effect.
In concrete, I tried changing the format of the fields to numeric, tried to
copy the values to a different location (only values, no formats at all),
but the sum formula does not do the total nevertheless.

Curiously enough, I tried entering all the values again (in a parallel
column), and the total is working this time -- in fact, this is the only
solution I could find until now, however inconvenient this is.

To help find the source of the problem, have you tried using a
formula to compare the original numeric column with the one you
have entered manually, cell by cell. If any of these pairs are
not equal, then perhaps this points to what is preventing the SUM
from working.

What actually appears in the SUM cell?

Chris

 

[Sequoia]

I found it at http://www.ozgrid.com/forum/showthread.php?t=56299!!!!!
Full credit to Bill Manville, who posted this solution in the MS newsgroups.


STEP #1: Create a new macro, and put the following code in it.

Sub LoseQueryNames()
Dim oName As Name
For Each oName In ActiveWorkbook.Names
If InStr(oName.RefersTo,"XLQUERY.XLA")>0 Then
oName.Delete
End If
Next
End Sub

My error was the following:

Cannot find 'C:\Program Files (x86)\Microsoft Office\Office12\LIBRARY\analyst.xla'AnalystSheetClose, which has been assigned to run each time abc.xlsx is closed. Continuin could cause errors. Cancel closing abc.xlsx?


STEP #2:
Instead of using XLQUERY.XLA in the Macro, I used C:\Program Files (x86)\Microsoft Office\Office12\LIBRARY\analyst.xla. Use whatever Excel gives you in the single quotes.


STEP #3:
Run the macro!

It worked for me BEAUTIFULLY!