UKC

Help with formulae please!

I have a long formula that references multiple cells in order to sum them. These cells are all in the same column - let's say AA1, AA5, AA9 etc.

Manually completing these for additional rows is painful so I want to copy and paste the formula for the next row and I need to sum AB1, AB5, AB9 etc. Instead it wants me to sum AA2, AA6, AA10 etc.

How do I do this?

Help appreciated.

In reply to Rampikino:

Stick $ in front of the bits of the cell reference you want to stay the same - so $AA1 when you copy it to another column will still be $AA1

$AA$1 would still be $AA$1 in another column and another row

hope that makes sense - googling absolute cell references probably gives a better explanation

In reply to Rampikino:

Assuming the cell in which you are making the formula is in the same column as the column you want to carry out the calculation in, you should be able to complete the first formula manually as you describe, then use "Fill Across" to populate the rest.

In reply to Rampikino:

Thank you guys, I think this helps!

In reply to Rampikino:

If you wanted your summed rows to be more configurable, you could include a new column AC ("Include in Total"), put a TRUE in the rows you want to be included, then in another cell use the formula "sumif($AC:$AC,TRUE,AA:AA)", you can drag that to the right and it will produce "sumif($AC:$AC,TRUE,AB:AB)" for your next column, that's what I might do if it were valuable to easily see, or easily change the rows used in total,

Off topic - So much is done with Excel, it's often not the appropriate tool, but probably has one of, if not the biggest impact on business after Windows...

In reply to Rampikino:

You were pasting the cell into the row below so it assumed you wanted to reference the row below. If you pasted into the column to the right instead it would have worked