Formulas

[Malleeboy]

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  

[#2]

@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

[#3 · Accepted 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:

Hope that helps

[#4]

@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)

Hope that helps

Nabil Mourad

[#5]

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

[#6]

@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