Including a variable number of columns in Excel Sum formula

[Richard Buttrey]

Can anyone suggest a formula, array or otherwise, which will achieve
the following.

I have 24 columns of data, two for each future month. As we progress
through a financial year I want to be able to add up the remaining
months of the year.
i.e. In Month 1, I need to include 22 columns; in month 2, 20 columns;
month 3, 18 columns etc..

I already have a variable set which represents the current month so
would like this to be included in the formula.

I could code this with a VBA routine, but feel there must be a
relatively straightforward formula which would achieve this, and as an
added benefit would be more readable by a user of the workbook.

TIA

Richard Buttrey

 

[ALEX]

Using a dynamic array, and setting the anchor to a chaning cell.

something like this:

Dyanamic_Array_Name = offset(This_is_a_changing_anchor, 0,0,
counta(some_range), counta(otherrange))

Just a thought.

 

[Chris.F]

Have you looked at OFFSET?

Assuming all the data starts at a10 and you have the current month in a9

SUM(offset(a10,0,(a9-1)*2)ffset(a10,0,23))
Regards
Chris