SOLVED

SUM!?

%3CLINGO-SUB%20id%3D%22lingo-sub-2925801%22%20slang%3D%22en-US%22%3ESUM!%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2925801%22%20slang%3D%22en-US%22%3E%3CP%3EWhere%20am%20I%20going%20wrong%3F%20%26nbsp%3BI've%20used%20Excel%20for%20years.%20%26nbsp%3BWhen%20it%20comes%20to%20adding%20up%20cells%20in%20the%20column%20above%2C%20I've%20merely%20inserted%20%3D%20and%20then%20clicked%20on%20each%20above%20cell%20which%20highlights%20in%20a%20different%20color.%20%26nbsp%3BNo%20inserting%20a%20%2B%20in%20each%20cell%20and%20no%20option%20key.%20%26nbsp%3BThis%20is%2C%20obviously%2C%20a%20fundamental%20function%20and%20surely%20there%20exists%20a%20setting%20for%20such.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20and%20thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMac%2010.13.6%3C%2FP%3E%3CP%3EExcel%2016.43%20(2019)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2925801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2925833%22%20slang%3D%22en-US%22%3ERe%3A%20SUM!%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2925833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1206712%22%20target%3D%22_blank%22%3E%40cornucopia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(YourRange)%3CBR%20%2F%3E%3DSum(A2%3AA100)%20%26gt%3B%26gt%3B%20will%20sum%20everything%20from%20A2%20to%20A100%3CBR%20%2F%3E%3DSum(A2%3AA10%2CA15%3AA20)%20%26gt%3B%26gt%3Bwill%20sum%20everything%20from%20A2%20to%20A10%20then%20sum%20A15%20to%20A20%3CBR%20%2F%3E%3DSum(A2%2CA4%2CA6%2CA8)%20%26gt%3B%26gt%3B%20will%20sum%20the%20values%20inside%20A2%2C%20A4%2C%20A6%20and%20A8%3CBR%20%2F%3E%3DSUM(A%3AA)%20%26gt%3B%26gt%3B%20will%20sum%20all%20the%20values%20from%20column%20A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20attachment%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2926003%22%20slang%3D%22en-US%22%3ERe%3A%20SUM!%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2926003%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20prompt%20response%2C%20Juliano.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20what%20you%20are%20suggesting%20is%20what%20I%20am%20trying%20to%20avoid.%20%26nbsp%3B%20I've%20never%20had%20to%20do%20such%20in%20years%20of%20Excel%20use.%20%26nbsp%3BI%20want%20to%20insert%20%22%3D%22%20and%20then%20click%20on%20each%20cell%20above%2C%20independent%20of%20one%20another.%20%26nbsp%3BEach%20cell%20should%20highlight%20in%20its%20own%20color.%20%26nbsp%3BOnce%20I%20click%20%22return%2C%22%20all%20the%20above%20highlighted%20cells%20give%20me%20the%20sum.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20must%20be%20a%20setting%20for%20this%2C%20but%20where%3F%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2926067%22%20slang%3D%22en-US%22%3ERe%3A%20SUM!%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2926067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1206712%22%20target%3D%22_blank%22%3E%40cornucopia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20needs%20some%20delimiter%20to%20separate%20the%20range%20as%20you%20click%20in%20different%20cells.%3C%2FP%3E%3CP%3EIf%20you%20just%20type%20%22%3D%22%20and%20start%20to%20click%2C%20excel%20will%20%22understand%22%20that%20the%20last%20clicked%20cell%20is%20the%20cell%20you%20want%20to%20refer.%3C%2FP%3E%3CP%3EEven%20with%20delimiter%20you%20need%20to%20tell%20to%20excel%20what%20operation%20or%20action%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20only%20one%20way%20(without%20vba)%20you%20can%20achieve%20it.%20But%20for%20me%20takes%20more%20time%20than%20just%20typing%20a%20formula%3C%2FP%3E%3CP%3EBecause%20you%20will%20need%20extra%20clicks%20by%20coming%20and%20going%20between%20%22box%20numbers%22%20on%20each%20click%20of%20the%20cell.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JulianoPetrukio_0-1636022498751.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F324192i3605EEE3F2525667%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JulianoPetrukio_0-1636022498751.png%22%20alt%3D%22JulianoPetrukio_0-1636022498751.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Where am I going wrong?  I've used Excel for years.  When it comes to adding up cells in the column above, I've merely inserted = and then clicked on each above cell which highlights in a different color.  No inserting a + in each cell and no option key.  This is, obviously, a fundamental function and surely there exists a setting for such.

 

Please and thank you!

 

Mac 10.13.6

Excel 16.43 (2019)

11 Replies

@cornucopia 

=SUM(YourRange)
=Sum(A2:A100) >> will sum everything from A2 to A100
=Sum(A2:A10,A15:A20) >>will sum everything from A2 to A10 then sum A15 to A20
=Sum(A2,A4,A6,A8) >> will sum the values inside A2, A4, A6 and A8
=SUM(A:A) >> will sum all the values from column A

 

Find attachment

@Juliano-Petrukio 

 

Thank you for your prompt response, Juliano.  

 

Unfortunately, what you are suggesting is what I am trying to avoid.   I've never had to do such in years of Excel use.  I want to insert "=" and then click on each cell above, independent of one another.  Each cell should highlight in its own color.  Once I click "return," all the above highlighted cells give me the sum.

 

There must be a setting for this, but where???

@cornucopia  wrote:  ``I've merely inserted = and then clicked on each above cell which highlights in a different color.``

 

I am not familiar with Excel for Mac.  But I am quite sure that Excel for Windows has never behaved exactly that way.  I suspect that you forgot some details.

 

In any case, in my version of Excel (Excel 2010 for Windows), the following works as you describe for my example:  select E5; click AutoSum (upper right) -- no need to type "="; press and hold Ctrl, then click each cell; release Ctrl and press Enter.  Caveat: AutoSum might be in a different position of the toolbar in your version.

 

autosum right-click.jpg

 

Note: The image assumes that you selected the option "Allow editing directly in cells".  In my version of Excel, click File > Options > Advanced.  Otherwise, the colored cell names appear in the Formula Bar.

@cornucopia 

Excel needs some delimiter to separate the range as you click in different cells.

If you just type "=" and start to click, excel will "understand" that the last clicked cell is the cell you want to refer.

Even with delimiter you need to tell to excel what operation or action you want.

 

There is only one way (without vba) you can achieve it. But for me takes more time than just typing a formula

Because you will need extra clicks by coming and going between "box numbers" on each click of the cell.

JulianoPetrukio_0-1636022498751.png

 

 

Even that way its necessary to type the delimiter (comma). Because when A1 is selected and then you select B1, without delimiter it using comma, the last click will be the range for the formula.

@Juliano-Petrukio  wrote: ``Even that way its necessary to type the delimiter (comma).``

 

No. But I made a critical error when I typed the description.  I edited the posting with the correction.  Reread and try again.  Sorry.

best response confirmed by allyreckerman (Microsoft)
Solution

@cornucopia 

I'm also not a Mac user. Base on this thread  Just click individual cells to sum in Excel (microsoft.com) such functionality on Mac disappeared with introduction of 2016.

@Sergei Baklan 

 

Thank you, Sergei.  That solves the problem.  Finally someone understood what I was getting on about!  Why Microsoft would exclude this function is beyond me.  It was ideal.  But then again, I ask myself why Microsoft has so many bells and whistles in everything they create (e.g., this forum!)

@cornucopia Not too long ago it popped up again, at least in MS365. Under Excel, Preferences, Edit you can Enable "Click to add mode".

Screenshot 2021-11-04 at 14.38.55.png

@Riny_van_Eekelen 

 

Dear Riny, 

 

Thank you.  

 

I. sorry but I do not find any "Click to add" in my Preferences or anywhere else in Microsoft Excel for Mac, v. 16.43

@cornucopia I'm currently on 16.56 (Insider, Beta). Can't remember when the Click to Add mode appeared, but it was about 6 months ago. Not sure though.