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!
Jun 10 2018 08:47 AM - edited Jun 10 2018 08:47 AM
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
Jun 10 2018 06:57 PM
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!
Sep 08 2018 12:42 AM
Have you updated existing spreadsheet to current Excel version?
May 28 2019 12:02 PM
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
May 28 2019 12:19 PM
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.
May 28 2019 12:31 PM
Hi. Thank you, I´m still not able to do it though. Because b) i found, but to change to numbers doesn´t seem to work and nothing new happens.@Deleted
May 28 2019 12:39 PM
Excel is fussing because you're not telling what cells to sum. You need a range of cells in your formula. Instead of =SUM(), you need something like =SUM(N2:N20) or whatever your range is.
May 28 2019 12:41 PM
@Deleted , if you'll check screenshort more carefully you see what Home tab is selected.
May 28 2019 12:47 PM - edited May 28 2019 12:51 PM
Yes, I know that. But the starting point of the rightmost arrow seems to indicate that one should start by clicking the Data ribbon and is thus misleading (as is evident from hannaaaase's comment "when I click "data". There is no place to hit numbers").
Not bashing anyone here....just trying to clarify for hannaaaase. @Sergei Baklan
May 28 2019 12:47 PM
@hannaaaase , could you please check
Win+R, type control international, here additional settings and what is you decimal separator
or simply check =ISTEXT(N1) in any empty cell
May 28 2019 05:27 PM
Hi @Deleted,
I hope you next time to choose more appropriate terms because the term (misleading) is often used for deceptive and intended fraud.
The polite term that you should say is that the screenshot might not be clear enough!
Although I think it is clear enough because all Excel users are supposed to know that selected ribbon tab in the screenshot is the Home tab because it's popular, mostly used, and it's the first tab that appears to the user when he opens Excel.
Regards
Aug 16 2019 08:38 AM
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.
Aug 16 2019 08:40 AM
On your screen shot it almost looks like the sum formula is part of the marked cells to sum.
Aug 16 2019 08:52 AM
Replying to myself for future readers:
I saved and closed Excel. When I re-opened it popped up a message that there was a circular reference. Once I fixed it, the Auto Sum worked.
Phew!
Jun 25 2020 07:18 AM
@Meg0422 I did everything listed in this chat and my autosum is still not working in every column
Jul 11 2020 03:20 AM - edited Jul 11 2020 03:39 AM
Auto Sum means you do not have to tell it what ccells to sum. You select the range of cells and click Auto Sum. It does not work!!
Even if you then manually enters the range (i.e. you are using SUM) it still does not add. It gives me mostly R0.00, or nothing at all.
Jul 11 2020 03:30 AM
OK, that seems to be the problem. I copied currency from another document where the decimal was indicated by a . and not a ,
However, I did format the column to be currency, so Excell should either have changed it or alerted me to the problem!