AutoSum not working

[Margot Rylah]

I've been using Excel with AutoSum for many years.

Recently installed Office 365 and pre-existing spreadsheets are working as usual.

Have just set up a brand new spreadsheet with lots of cells to add.   AUTOSUM IS NOT WORKING!

Have done the following:   Click on cell to show total.  Click AutoSum > cursor through the row of cells to be added.  Press Enter and this formula comes into the cell instead of the total of the numbers in the cells.    =SUM(C8: AD8)

Have gone to File, Options, Formulas, Automatic > no response

Have checked format of all cells to be number, no decimal > no response. 

HELP???????????

Thanks!

[#2]

@Haytham Amairah 

There is a difference between misleading information and intentionally misleading information. A picture /screenshot/description may be misleading without being intentionally misleading. Only intentionally misleading is fraud. This is not a court, social media, or a financial statement. Misleading is not an accusation but merely indicating that the information displayed will confuse somebody who is stressed and in a hurry to sort a problem out. It is to help you to in future be more careful of the screenshot.

[#3]

@kgal805 

I had the same issue with AutoSum not working and all of these solutions not working.  It appeared that some imbedded formatting of the cells would not disassociate from these cells in spite of reformatting them every way possible, such as General, Text, Numbers, Currency etc.

I finally found a solution, as I realized that Excel does not have the same "Paste Special" options aw MS Word, specifically the "Unformatted Text" option.  By simply "copying" the column of numbers I wanted to AutoSum into MS Word, and "pasting" them using the "Paste Special" option, then selecting the "Unformatted Text" into the word document, then copying them and pasting them back into Excel, the AutoSum worked.

Essentially, pasting in Word as Unformatted Text discards the hidden formatting that Excel misses when formatting cells.  Yay!  Solution found!

Barry

[#4]

@Margot Rylah Here's my 2 cents - you can't sum merged cells. 

[#5]

@Leonard__ 

Why not? - you can

[#6]

@Sergei Baklan 

Hi Sergei - thanks for your reply.

I'm no expert but I have been using Excel for many years - many versions on many machines at home and work. I've always found Excel to be a bit flaky usually cured with a hard reboot when it goes weird occasionally (whine). This is the first time I've noticed a problem with merged cells. Maybe it's some anomaly on my machine? It's an i7 3.2 GHz with 16 GB RAM, 64 bit Win10 and Excel 2019.

I'm pretty sure I have used auto sum on merged cells in the past on different machines with different versions of software. I found on my new machine with a column of single cells I can highlight them and auto sum - no problem. However, if I merge 2 cells horizontally and then have a column of these merged cells - auto sum does nothing. No errors no action. Cells were formatted as currency in both examples.

Just a for-what-it's worth. I came across this out of my own frustration - didn't see it on Google which also seems weird like I'm the only one?

Hope that was helpful.

Bye

[#7]

@Leonard__ 

First, I'd do not recommend to use merged cells, especially horizontal ones, at all. Merged cells are root for many issues. Use Center Across Selection where possible.

For such sample

if autosum only merged cells, SUM takes entire range but result is correct since for merged cells values are kept in left most cells and others are empty.

If autosum mix of merged and not merged cells - yes, you need to adjust the range manually to use only left column where the values are.

[#8]

@barrymdho  Success! It worked, but we shouldn't have to go through all that to autosum!

[#9]

@Margot Rylah  Hello! It happened because your numbers were in text format. I had to multiply two columns. I derived the first column by multiplying two other columns using the IMPRODUCT function. Maybe that's why Excel thought the result was a text. So, instead, I multiplied using the usual formula method, =B1*C1. The resultant number was in number format! AutSum worked perfectly fine then.

[#10]

@alphamanny , I think we addressed the formatting issue, where many had formatted to number or currency, where the auto sum still did not work. In my  case, I had formatted to number or currency and still the auto sum would not work, until I copy pasted into MS Word, cleared all formatting, then pasted back into Excel. FYI

[#11]

@barrymdho 
Is this the only solution ie copying the values from xcel and then pasting them in .doc and repeat the process to excel? I tried other options but this is the only one which works......

[#12]

Incorrect. Cells are in General format.

[#13]

After much experimenting, I found that downloading bank data "infected" the cells, including ones that look blank. In general, they "push" the Sum results down to below the last cell that is not empty or not infected, or some similar effect.  Changing the format has no effect.  Selecting infected cells then clicking delete removes the infection.

Hopefully you can at least partially understand my cryptic diagram.

[#14]

@Haytham Amairah 

Not to be rude, but I too could not follow the screen-shot. I am still having this problem and it is very frustrating.

[#15]

None of the suggestions have worked for me. I am not sure what changed but it is extremely frustrating.

[#16]

I’ve been using Excel for years and years. It’s always been a little flaky. I’ve done a lot of rebooting to make Excel happy again.
I’ve noticed you can’t auto sum if it includes a merged cell. I don’t think it used to be like that?

[#17]

@mlshunt 

Don't worry about thinking you might be rude.  I agree with you that what I posted was very cryptic and very difficult to understand.  I was tired of trying to figure it out and ran out of energy to make it understandable.  When I have some time I'll give it another go.

John

[#18]

@Margot RylahI am not sure if this has been resolved yet but I have just had the same issue and found the problem was having spaces or tabs before the number I wanted to sum. Deleting the spaces allowed the cell to be formatted correctly to numbers and autosum started working again.

[#19]

@Jaybe1972 Gotta be numbers or currency. Sometimes merged cells won't work either.

[#20]

@Leonard__I know but that still won't work if you have spaces before the number. A lot of people have had this problem when copy/pasting from text documents and no amount of formatting will work unless the spaces are removed :)

[#21]

@Leonard My auto sum does not auto sum as information is entered in excel.  All cells are in account format.  Should it auto sum as the info is entered or am I expecting too much?  I tried to follow all the above info but as my username says not tech savvy.