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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies