In my Excel spreadsheet, I've got a cell that is =SUM(C6:C19). If I go to C20 and add a row, that formula will not adjust. I want it to change to =SUM(C6:C20)
views:
133answers:
2
+2
A:
Rather than use a static range in the formula you can use a dynamic range:
=SUM(OFFSET(reference, rows, cols, height, [width]))
For example:
=SUM(OFFSET('My Sheet'!$C$6, 0, 0, COUNTA('My Sheet'!$C:$C)))
This assumes nothing else is in column C. You can restrict the height range if necessary*:
=SUM(OFFSET('My Sheet'!$C$6, 0, 0, COUNTA('My Sheet'!$C$6:$C$30)))
*From Lunatik's comment
Patrick Cuff
2009-03-18 13:14:43
Bear in mind that this assumes nothing else is in column C though. You can restrict the height range if necessary, e.g. =SUM(OFFSET('My Sheet'!$C$6, 0, 0, COUNTA('My Sheet'!$C$6:$C$30)))
Lunatik
2009-03-18 13:56:03
Yes, thanks Lunatik. Answer updated accordingly :)
Patrick Cuff
2009-03-18 16:43:13
A:
The simplest way is to leave a one cell space between the bottom of your values and the sum formula.
For example, Cell A20 currently sums cells A1 to A19. in you insert a row below A19, the sum won't won't include the new row right?
Well, if you put the sum formula in cell A21 and sum from A1 to A20 (even though a20 hasn't got anything in it), when you insert a row above cell A20 your sum formula will include it.
The offset thing mentioned as an alternative answer is a great way to do it also, but is more confusing, this is what I do if I want to keep it simple.
Matthew Rathbone
2009-03-19 08:04:21
this could still work, if C20 is blank make the sum c6:c20 then whenever you do an insert on c20 it will be included.
Matthew Rathbone
2009-03-19 17:03:18
The ideal way would be to have calculations on a different sheet, then you could just do a sum on the entire column
Matthew Rathbone
2009-03-19 17:04:01