Sum formula only works if placed directly below values??

[ericblair]

Here are my steps:
1: Open a sheet and highlight a cell
2: Open the Format box and enter this: [$-10409]$- , click OK
3: Enter 0 in that cell, note that the format takes effect and $- is displayed
4: Right below that cell enter 1 and format it as a Number
5: Highlight the cell right below that second cell and click AutoSum and hit Enter

The result I expect is 1, but instead a $- is displayed.

What's going on here?

 

[ericblair]

After some playing around it looks like what's happening is that the Sum function is not format agnostic. All cells that you are summing up must have the identical formatting.

 

[mgirvin]

It is not that:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
"All cells that you are summing up must have the identical formatting."<o></o>
<o></o>
What is happening is that the SUM function "sucks" the formatting from the top cell in the range that has the formatting:<o></o>
<o></o>
[$-10409]$-<o></o>
<o></o>
If you do your same procedures (1 to 5) and add a sixth step, it will work:<o></o>
<o></o>
1: Open a sheet and highlight a cell
2: Open the Format box and enter this: [$-10409]$- , click OK
3: Enter 0 in that cell, note that the format takes effect and $- is displayed
4: Right below that cell enter 1 and format it as a Number
5: Highlight the cell right below that second cell and click AutoSum and hit Enter
6: Apply a Number format to the cell with the SUM function.<o></o>
<o></o>
The way I think of Number formatting and formulas is that "The calculated result of a formula doesn't look at formatting, but the Number format can disguise the calculated result temporarily." In your case with the added step 6, we can see that the calculated result of 1 was just temporally disguised and when the Number format is applied you can see that the formula does calculate correctly.<o></o>