AutoSum sometimes doesn't work?

[John]

Hello.

If I have a spreadsheet with figures in eg cells A1-7 and in cell A9 I
click the AutoSum button in the toolbar, it will bring up the formula
=SUM(A1:A8), then I just hit the enter key and it totals it all up for
me which is great.

However, sometimes I have spreadsheets with the exact same scenario,
except when I press the AutoSum button it only brings up the following
=SUM( ) and nothing else. If I manually type in the range into the
brackets eg A1:A8 and press enter it doesn't work for some strange
reason.

Does anyone know why it might not bring up the range in the first
instance, and why if I type it in myself it wont work?

It's really quite strange how sometimes it works and sometimes it
doesn't.

Nothing else with the format of the actual column appears to be any
different when I go to Format and Cells and check everything.

If you can shed any light on that strange one I'd be very grateful

Cheers

John

 

[Don Guillett]

I assume that you are SELECTING more than 1 cell when you do this.
When you select a1:a2 and touch the button you get the sum in a3.

 

[Debra Dalgleish]

Perhaps the figures are being stored as text, instead of numbers. To
convert them to numbers:

1. Select an empty cell on the worksheet
2. Choose Edit>Copy
3. Select cells A1:A7
4. Choose Edit>Paste Special
5. Select Add, click OK

Now, test the AutoSum in cell A9, and see if the problem is solved.

 

[Gord Dibben]

John

The Autosum behaves this way if one or more of the entries in your range are
perceived by Excel to be Text.

Sometimes what looks like a number is Text. Perhaps the range was
pre-formatted as Text before entry. Or the data was imported as Text.

Copy an empty cell. Then Edit>Paste Special>Add>OK>Esc. This will generally
convert the Text to real numbers.

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[John]

Thanks guys.

The solution works.

It must of been entered as text, sometimes you just can't tell with
figures.

John

 

[wfotto]

I copy/paste data from my bank account, which does seem to bring in values as text, with some strange prefix charactor. I remove the charactor and it works great as a number, using simple addition. But if the same cell is used in an AutoSum calculation it ignores it. I check, and the format is number. The only ways to fix it is to re-enter the same number (not formatting change), or double-click on the cell and hit return.