Making a SUM formula with variable sum range

[L-Drr]

Hi all,

I'm looking for a way to make a SUM formula that shifts it's range (in a non-standard way) when I copy it to other cells.
Each sum has a vertical range above it. The range of the sum 1 column to the right, should start 1 row lower (but end in the same row).
The attached example file will probably make more sense than this explanation

I've experimented with OFFSET, INDIRECT, MID("ABCD...";COLUMN(B3);1) etc etc but I can't get it right...
Who can help me?

Thanks a lot,
Leon

[Glenn Kennedy]

In B8, copied across:

=SUM(OFFSET(B7,,,-5+(COLUMNS($A:A)-1),))

[L-Drr]

Wow Glenn,

There are so many thing in your formula I didn't know about
But I managed to get it working in my actual file, so thanks a lot!!

I've simplified it a little bit
- merged your "-5+x-1" to "x-6" (although your version follows the line of thought)
- changed your "COLUMNS($A:A)" to "COLUMN(A1)"

I also didn't know you could leave the 2nd and 3rd argument in the OFFSET function empty; in the Dutch excel-help they seem te be mandatory
I still don't fully understand how the OFFSET-part of the formula results in something like "B3:B7" as the only argument in the SUM formula, but hey, it works

Once again thanks for providing the formula and teaching me a thing or two!

[Glenn Kennedy]

merged your "-5+x-1" to "x-6" (although your version follows the line of thought). Exactly why I set it out the way that I did. If look at it in 6 months time, you won't remember what you did... or why.

To be really fussy, you should use:

=SUM(OFFSET(B7,,,-5+(COLUMNS($B:B)-1),))

in either your version, or my previous version.... delete column A and weep.

[L-Drr]

Hahaha very true XD