Simple (?) Excel formula troubles

  Mike D 16:29 19 Mar 2010

Help, my sparse hair is becoming even more endangered.
We are using Excel XP. Using Autosum I get a total for A1:D1 in I1 and a total for E1:H1 in J1. Simples but, after entering values in A1,B1,C1 and D1 and getting the total in I1, a value entered in E1, F1, G1 or H1 is not only totalled in J1, but is also added onto the original total in I1. If values are entered into E1:H1 before entering values in A1:D1 everything works fine. However we do not always get the second group of values for entering before the first group are available.

What is even more puzzling is that for the first 100 or so rows it all worked properly but now, whether we copy the formulae down or enter a new formula for each row we get the same problem.

  MAJ 16:55 19 Mar 2010

Don't use Autosum, it will take in all values from A1 to I1. Use =SUM(A1:D1) in I1 and =SUM(E1:H1) in J1. If I've read you correctly that is.

  Mike D 17:00 19 Mar 2010

Tried that, it makes no difference. this is what is so frustrating.
Thanks any way.

  John B 17:29 19 Mar 2010

I just tried and what seems to happen is Excel modifies the formula in I1 to include a greater range. When I corrected the formula it stayed the same afterwards.

I wonder if using the $ symbol would help?

  John B 17:32 19 Mar 2010

=SUM(A$1:D$1) seems to work

  Mike D 17:37 19 Mar 2010

Doesn't seem to help because, whilst it lets you keep to two ranges for the first row, the formulae cannot be copied down because of the absolute cell references, which means that the formulae in each row have to be typed in seperately - we are currently on row 249!

  VoG II 17:41 19 Mar 2010



copied down. This fixes the columns rather than rows.

  Mike D 17:51 19 Mar 2010

Thanks very much.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?