# Sum formula only works if placed directly below values??

#### ericblair

##### New Member
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?

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.

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>

Replies
0
Views
269
Replies
27
Views
1K
Replies
8
Views
331
Replies
4
Views
236
Replies
1
Views
365

1,214,285
Messages
6,118,678
Members
448,845
Latest member
MrEbzz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back