SOLVED

Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-776102%22%20slang%3D%22en-US%22%3EFormulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776102%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20using%20Excel%20for%20many%20years%20but%20just%20in%20the%20last%20couple%20of%20days%2C%20something%20has%20happened%20to%20the%20Formula%20function%20in%20my%20spreadsheets.%20I%20set%20the%20formula%20to%20add%20boxes%20A1%20through%20to%20say%20A20%20in%20box%20A22.%20That%20works%20fine%20but%20when%20I%20try%20to%20drag%20the%20%22Total%22%20box%20downwards%20to%20B22%2C%20C22%2C%20etc.%2C%20all%20I%20get%20is%20a%20repeat%20total%20of%20A22%20inspite%20of%20the%20data%20being%20different!%20Any%20ideas%20how%20to%20fix%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EMalleeboy%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-776102%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776111%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383077%22%20target%3D%22_blank%22%3E%40Malleeboy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20that%20sum%20formula%20in%20column%20A%20is%20freeze%20%3DSUM(%24A%241%3A%24A%2420).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20just%20need%20to%20remove%20%24%20sign%20(%3DSUM(A1%3AA20))%20from%20that%20formula%20and%20then%20drag%20it%20to%20B22%20or%20C22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20you%20may%20share%20your%20file%20so%20issue%20can%20be%20directly%20fixed%20in%20the%20file.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776118%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776118%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383077%22%20target%3D%22_blank%22%3E%40Malleeboy%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20probably%20have%20the%20calculation%20option%20in%20the%20manual%20state!%3C%2FP%3E%3CP%3EPlease%20make%20sure%20the%20calculation%20option%20is%20set%20to%20%3CSTRONG%3EAutomatic%3C%2FSTRONG%3Eas%20follows%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124691iB7E870776F18636D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Automatic%20Calculation%20Option.png%22%20title%3D%22Automatic%20Calculation%20Option.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776130%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383077%22%20target%3D%22_blank%22%3E%40Malleeboy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EOne%20more%20option%20to%20add%3C%2FP%3E%3CP%3EAssuming%20that%20you%20have%20%3CSTRONG%3Evalues%20From%20A1%20to%20E20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESelect%20all%20the%20source%20values%20from%20A%20To%20E%20but%20when%20you%20drag%2C%20drag%20down%202%20more%20rows%20(%3CSTRONG%3EA1%3AE22%3C%2FSTRONG%3E)%20so%20you%20are%20actually%20%3CSTRONG%3Eincluding%20the%20destination%20row%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThen%3C%2FP%3E%3CP%3EOne%20Click%20on%20the%20AutoSum%20tool%20(either%20Home%20Tab%20or%20Formula%20Tab)%20%26gt%3B%26gt%3B%20%3CSTRONG%3EWe%20are%20done!!%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENB%3A%20alternative%20to%20clicking%20the%20AutoSum%2C%20and%20with%20the%20same%20selection%3A%3CBR%20%2F%3EUse%20the%20Shortcut%3A%20%3CSTRONG%3EALT%20and%20Double%20Click%20on%20the%20EQUAL%20sign%3C%2FSTRONG%3E%26nbsp%3B%20(ALT%2C%20%3D%2C%3D%26nbsp%3B%20Fast%20and%20simultaneously)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20713px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124692iC10B0262A8B503CD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22AutoSum.png%22%20title%3D%22AutoSum.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776137%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776137%22%20slang%3D%22en-US%22%3EYou're%20right%20Haytham%20....%20thankyou%20sincerely.%20I%20wonder%20who%20changed%20that%3F%20I%20certainly%20didn't!%20Gremlins!!%20Anyway%2C%20fixed%20now%20very%20speedily%20so%20your%20assistance%20is%20much%20appreciated.%20Thanks%20to%20the%20others%20as%20well%20for%20their%20advice.%3CBR%20%2F%3ECheers%2C%3CBR%20%2F%3EMalleeboy%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776145%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383077%22%20target%3D%22_blank%22%3E%40Malleeboy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThere%20are%20several%20reasons%20for%20this%20change!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIt%20may%20be%20due%20to%20an%20add-in%2C%20a%20macro%2C%20or%20a%20bug%20in%20Excel.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EPlease%20check%20out%20this%20related%20%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fwhy-does-the-calculation-mode-keep-changing%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Earticle%3C%2FA%3E.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ERegards%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Malleeboy
New Contributor

I have been using Excel for many years but just in the last couple of days, something has happened to the Formula function in my spreadsheets. I set the formula to add boxes A1 through to say A20 in box A22. That works fine but when I try to drag the "Total" box downwards to B22, C22, etc., all I get is a repeat total of A22 inspite of the data being different! Any ideas how to fix this?

 

Thanks,

Malleeboy  

5 Replies

@Malleeboy 

It seems that sum formula in column A is freeze =SUM($A$1:$A$20).

 

You just need to remove $ sign (=SUM(A1:A20)) from that formula and then drag it to B22 or C22.

 

Alternatively you may share your file so issue can be directly fixed in the file.

Thanks

Solution

@Malleeboy

 

Hi,

 

You probably have the calculation option in the manual state!

Please make sure the calculation option is set to Automatic as follows:

Automatic Calculation Option.png

 

Hope that helps

@Malleeboy 

Hi

One more option to add

Assuming that you have values From A1 to E20

Select all the source values from A To E but when you drag, drag down 2 more rows (A1:E22) so you are actually including the destination row

Then

One Click on the AutoSum tool (either Home Tab or Formula Tab) >> We are done!!

NB: alternative to clicking the AutoSum, and with the same selection:
Use the Shortcut: ALT and Double Click on the EQUAL sign  (ALT, =,=  Fast and simultaneously)

AutoSum.png

Hope that helps

Nabil Mourad

You're right Haytham .... thankyou sincerely. I wonder who changed that? I certainly didn't! Gremlins!! Anyway, fixed now very speedily so your assistance is much appreciated. Thanks to the others as well for their advice.
Cheers,
Malleeboy

@Malleeboy 

 

There are several reasons for this change!

It may be due to an add-in, a macro, or a bug in Excel.

Please check out this related article.

 

Regards

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies