# Thread: Changing Sum Formula based on Number of Months

1. I have a worksheet which I have attached with a formula to add up the previous month data based on the number of Months in cell N1. The year runs from Oct to Sept. period 8 Represents the Period Oct to May of the previous year

I have use the formula =SUM(AH2:INDEX(2:2,34+\$N\$1-1)) to calculate the data from Oct 2007 to May 2008 and it is correctly calculated

The problem that i have is that each month, I new column will be inserted where column W is and the current months data will be copied into column W.

The formula must stay as =SUM(AH2:INDEX(2:2,34+\$N\$1-1)

The formula however changes to =SUM(AI2:INDEX(2:2,34+\$N\$1-1)) , where a new column is inserted

It would be appreciated if you would assist

Regards

Howard

2. You could use

=SUM(INDIRECT("AH2"):INDEX(2:2,34+\$N\$1-1))

or

=SUM(OFFSET(INDIRECT("AH2"),0,0,1,\$N\$1))

3. [quote name='HansV' post='783805' date='09-Jul-2009 17:45']You could use

=SUM(INDIRECT("AH2"):INDEX(2:2,34+\$N\$1-1))

or

=SUM(OFFSET(INDIRECT("AH2"),0,0,1,\$N\$1))[/quote]

Hi Hans

Thanks for the help. This is much appreciated

Regards

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•