Sum a range, with the range set by a cell entry.

Situation-I need to sum all of the prior month totals, which increment over time.

I know the rows, and I can calculate the columns...

For instance, It goes from D6:I37.

But now in the next month it goes from D6:J37

I already have a method to compute the end column based on the month whos value exists in the model. IE January would be 1+3, the 1 is available elsewhere in the sheet as a manual entry of the month to compose the report, the 3 offsets it into the array.

The question is, what goes inside the Sum()?

Maybe something like...
=Sum(D6:column(Month+3)&37)

Maybe that would work in VBA or something along those lines, but not in excel...

As the variation in your range is consistant by month you could use something like

=SUM(D6:INDEX(D37:R37,3+Month))

as an alternative to Dilip's suggestion.

Originally Posted by icestationzbra

you could also use OFFSET

Volatile fuctions are best avoided when possible.

well as I dug into your suggestions and attempted to implement it into the bigger picture, the offset seemed to be the one that worked.

What I didnt mention is that I was calling it into a sumproduct function...It works atleast.