Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

@SUM not working

Copper Contributor

I learned Excel waaaay back.  I have always typed in my formulas as @SUM and not =SUM.

For some reason the @ version is not currently working.  Did Microsoft phase this out?  I can't find anything on the web about it.

24 Replies

@ironhandyman1966 Am perhaps not old enough (born 1962) and worked with Excel for the past 38 years. Can't remember it was ever @ that designated a formula. Always used =. But perhaps my memory serves me wrong.

@ironhandyman1966 

 

I believe that is Lotus 1-2-3 syntax.

 

In Excel 2010, I can type a formula starting literally @SUM(B1:B10), and Excel changes it to =SUM(B1:B10).

 

And if I type =2+@SUM(B1:B10), Excel chanages it to =2+SUM(B1:B10), removing the "@".

 

In Excel Online (onedrive.live.com), @SUM(B1:B10) is changed to =@SUM(B1:B10).

 

And =2+@SUM(B1:B10) is unchanged.  The "@" is not removed.

 

But I would be wary of typing "@" explicitly.  Ironically, it now called the "implicit" (sic) operator.  Do a google search for more information on the legacy (truly) implicit operation.

 

Nevertheless, in the two examples above, Excel Online does sum the range.  If B1 is 1 and B2 is 20, the first form returns 21, and the second form returns 23.  Go figure!

 

(Rushed!  On second thought, I'm not surprised by that behavior with SUM per se, because takes a range parameter normally.)

 

In any case, demonstrate what you mean by "not working".   Show us screenshots.

If I type =SUM at the bottom of this column of numbers, select the range, and hit enter - the sum of 36.5 is displayed

If I type @SUM at the bottom of this column of numbers (this worked previously), select the range, and hit enter - @sum( is displayed. Thought maybe it was the excel update.

I don't see a way to attach pictures, but this is what the columns look like for each:

=SUM

4
5
9
11
7.5

36.5

@SUM

4
5
9
11
7.5

@sum(

@ironhandyman1966  wrote:  ``If I type @SUM at the bottom of this column of numbers (this worked previously), select the range, and hit enter - @sum( is displayed [....]``

 

I don't know if that ever worked the way that you describe literally.  But I'm not a fan of "shortcuts".  So I'm not very familiar their behavior.

 

What you describe sounds like the so-called AutoSum operation, sort of.  That was moved to the toolbar (ribbon) at least in Excel 2007 (2010) -- maybe even Excel 2003.  I cannot remember.  Before that, it might have worked as you describe.  I don't remember.

 

@Riny_van_Eekelen 

 

Am perhaps not old enough (born 1962)

 

You are so young!! You were one year old when I graduated from college!!

@ironhandyman1966 

Which version of Excel are you using?

In my desktop version of Excel in Microsoft 365 for Windows, @SUM(A1:A3) is automatically converted to =SUM(A1:A3).

But in Excel Online (in the browser), @SUM is not recognized, and results in #NAME!

@ironhandyman1966 

@SUM() works if you ON Lotus compatibility setting (OFF by default) here

image.png

With that you may enter @SUM(range) which automatically will be converted to =SUM(range) on Enter.

Please note, setting is applied to the specific sheet in opened file only.

 

@Sergei Baklando you have an idea how to fix this on Excel on a Mac?

@yoyoindigo Long time Mac user myself, though now mostly working on PC. The 'transition formula entry' option is not available on Mac, and I do not recall that you could ever start a formula on a Mac with the @ sign either, contrary to your suggestion in this post starting the formula with @ - Microsoft Community Hub.

Have been using @sum since the days of the Bondi Blue Macs. And as mentioned, it's just since the upgrade that @sum() no longer works. But I will keep looking until I am forced to concede, and then will just have to adapt to using the proper =sum(). Thanks for your assistance though.

@Riny_van_Eekelen 

I converted from Lotus 123 to Excel around 1998 and continued using the legacy @ symbol as the beginning to all formulas, despite knowing that Excel instructions say you should use the = sign.   I just discovered that @ no longer works and this old dog will have to learn a new trick.  ugh....   sounds like a simple fix, but probably like you, I type in formulas very quickly and without thinking and I fear this will be a real challenge (albeit a first world one :)     A quick google search indicates that Microsoft has adopted the @ sign to enable some "implicit intersection operator", so that's why they probably had to disable the legacy use of the symbol.   I guess maybe we need to reread "Who Moved my Cheese"...

@Sergei Baklan 

 

Thank you!

@Sergei Baklan 

This solution does not work.  I guess I am also ancient in age as I've ALWAYS used the @sum.  It is such a habit for me that this all of a sudden not working is extremely annoying!

Never mind - closed my sheet, re-opened, tried it and it DOES work. Thank you! (too bad it won't stick and I have to remember to do this everytime tho).

@DebraP  I feel your pain. I began using Lotus123 in the 80's and have always used the @ symbol in excel (and I am somewhat of a master at excel, so no dummy). the @ symbol stopped working for me around a week ago, after another company installation of cloud based Thompson Reuters app, requiring 2FA and update to my MS account. Not only can I not use @, my CTRL/spacebar  no longer highlights sections of data before encountering a blank row/column. There are a few other oddities that have not worked but they have been hit and miss and depends on which workbook I am in. It is sooooo frustrating. I cannot type =sum or anyother =formula, my fingers do not work that way!!! No one out there seems to notice as we are too old I guess and very few of us operate this way with @. Its my left hand that moves to hit @ as I'm whizzing thru typing, I cannot make my right hand find =. If I have to retrain my hands I am going to be VERY VERY UNHAPPY! and this still doesnt solve the CTRL/spacebar issue. Turning on the Lotus123 transition box has to be done for each different workbook. Oh how I am not going to do this. It has not been an issue for 40 years, why now??? Hope someone can come up with something or at least a reason why this just started occuring.

Read it. Can't do it, LOL. It's not just learning the new symbol, but it's an awkward reach. Ugh!
I feel you. The equal sign is a very odd and uncertain reach, however Microsoft has never cared about my opinion.

@ironhandyman1966 

IKR? I do believe my IT dept my have found a cause tho, not certain. I work in and outside of our 2 servers and thus utilize various versions of excel depending on where I am working. The major problems are stemming from version 2311 Build 17029.20108 which I believe I just upgraded to a week or so ago which is when my issues began. Another version I work on, version 2302 build 16130.20846, has some new weirdness to it but the @ works as well as SHFT/spacebar, up/down arrows. 

 

Just something to consider as we are thinking there are a few bugs in version 2311 MS needs to work out and then hopefully things will be back to normal for us old timers. LOL

Thanks so much for this! I've been using @sum() since the earliest days of Lotus 1-2-3 and it is a very difficult habit to change!!