Forum Discussion
AutoSum not working
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!
Hi Margot,
I frequently faced this issue before!
The problem is:
The cells are in Text format!
I suggest in this case to select the entire sheet from the triangle in right-hand corner, then select General Format as follows:
After that, go to the formula and update it.
Regards
Thanks Haytham Amairah. I had already realised that the cells were in text format and had changed the appropriate cells to Number format.
BUT .. I hadn't reformatted the cells into which the totals were to be entered, via AutoSum.
So .. THANK YOU! I'm greatly relieved!
I tried same. Didn't work!
Have you updated existing spreadsheet to current Excel version?
Tiffany McCarroll Are you saying that autosum in older versions of Excel have been disabled?
This isn´t working for me because I don´t get the same drop down menu when I click "data". There is no place to hit numbers. What do I do? Margot Rylah
The screenshot that Haytham posted seems to indicate that clicking the Data ribbon gives you an option to change the cell format. This is misleading. Instead, you can either (a) select the range of cells and click the Home ribbon to find the indicated format cells dropdown list or (b) select the range of cells then right-click the range and select Format (near the bottom). Finally, apply either General format or the relevant number format.
Deleted , if you'll check screenshort more carefully you see what Home tab is selected.
I just encountered this too.
I am using Auto Sum to look at cells that have already Auto Summed several other cells. I already checked and each one is set to currency. The cell range is correct, J113:J116. And my total to remit is $0, or $55,000+ but should be $62,725.
Accountants get cranky when AUTO SUM DOESN'T WORK. How do you even explain that to the Board of Directors? Oh, sorry guys, Excel doesn't work anymore. It's ok. I still have my trusty TI-5320SV CALCULATOR.
Technically this file is from before I subscribed to Office 365, but the format was original .xlsx and there doesn't seem to be an option for Office 365 formatting. Honestly, that shouldn't matter. Excel should always be able to take into account old formatting or pop up a message about it like it did at the last big programming change.
Side note - Someone suggested that the Auto Sum button on the ribbon shouldn't be used which is bogus. It's there to be used.
Margot Rylah Here's my 2 cents - you can't sum merged cells.
Why not? - you can
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
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.
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
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......
- Incorrect. Cells are in General format.
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.
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.
- This is because MS Office 365 is the worst product ever released by Microsoft. Nothing works.. It should be pretty simple to click on a Autosum Icon and work. Nope. Not with office 365. Worthless product.
Make sure there are no spaces in your numbers!
I found that, due to copying and pasting from a banking website, i had some extra blank spaces at the ends of numbers. They don't figure into the autosum function. Simply paste your column of numbers into Word or similar, and use the find and replace function to find all the spaces and replace with nothing. Re-paste back into excel and voila!
