Sum formula only works if placed directly below values??

ericblair

New Member
Joined
Sep 18, 2009
Messages
5
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

ericblair

New Member
Joined
Sep 18, 2009
Messages
5
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

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
It is not that:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
"All cells that you are summing up must have the identical formatting."<o:p></o:p>
<o:p></o:p>
What is happening is that the SUM function "sucks" the formatting from the top cell in the range that has the formatting:<o:p></o:p>
<o:p></o:p>
[$-10409]$-<o:p></o:p>
<o:p></o:p>
If you do your same procedures (1 to 5) and add a sixth step, it will work:<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,646
Members
430,150
Latest member
amitk1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top