SUM Function returns Zero in Error

[Joe R]

Has anyone encountered a situation where the SUM function
returns a zero amount for a given range in error?

I'm running Excel 2002 on XP Pro.
Auto Calc is on, I hit F9 also and no change.
I have reveiwed the format of the numbers and the format
is correct. I have also used the simpilest range possible
(i.e. =SUM(E6:E33) ) and there are no hidden rows.

The only way I can solve this is to save and close the
sheet. Then close Excel. Upon opening this sheet again,
the function works normally without any further
intervention.

Please let me know if this sounds familiar. Thanks!

 

[Raj]

Has anyone encountered a situation where the SUM function
returns a zero amount for a given range in error?

I'm running Excel 2002 on XP Pro.
Auto Calc is on, I hit F9 also and no change.
I have reveiwed the format of the numbers and the format
is correct. I have also used the simpilest range possible
(i.e. =SUM(E6:E33) ) and there are no hidden rows.

The only way I can solve this is to save and close the
sheet. Then close Excel. Upon opening this sheet again,
the function works normally without any further
intervention.

Please let me know if this sounds familiar. Thanks!

I see the same behaviour using Excel 2002 on XP Pro.

 

[David McRitchie]

Hi Joe,
Another test would be F2 then Enter to see if anything changes
to make use of a format included later than cell entry.


Checking the format may not be good enough, as it
only applies once a number is entered afterwards.
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
also see the table included.

check content with =ISNUMBER(E6)
and with =ISTEXT(E6)
to see what Excel actuall thinks it is.
A dead giveaway is if content is left aligned and none of the
left / center / right icons on toolbar shows up as being active.