Jun 10 2018 05:49 AM
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!
Jul 11 2020 03:37 AM
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.
Aug 20 2020 03:02 PM
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
Oct 18 2020 09:35 AM
@Margot Rylah Here's my 2 cents - you can't sum merged cells.
Oct 18 2020 06:21 PM
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
Oct 19 2020 12:10 AM
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.
Jan 15 2021 06:40 PM
@barrymdho Success! It worked, but we shouldn't have to go through all that to autosum!
Jan 25 2021 08:34 AM
@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.
Jan 25 2021 08:57 AM
@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
Mar 03 2021 12:25 PM
@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......
Feb 03 2022 08:03 PM - edited Feb 03 2022 08:22 PM
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.
Mar 09 2022 11:39 AM
Not to be rude, but I too could not follow the screen-shot. I am still having this problem and it is very frustrating.
Mar 09 2022 11:42 AM
Mar 09 2022 01:00 PM
Mar 09 2022 04:47 PM - edited Mar 09 2022 04:51 PM
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
Jul 04 2022 07:36 AM
@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.
Jul 05 2022 08:19 AM
@Jaybe1972 Gotta be numbers or currency. Sometimes merged cells won't work either.
Jul 11 2022 01:23 AM
@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 :)
Jul 17 2022 06:12 AM
@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.