Sum column A:? where ? is a number in a cell somewhere
 

I have a column for each month of the year and I need to compare this year
to date with last year to date and the previous year and the previous year
etc. I would like to enter a 1 to 12 in a cell somewhere that will allow me
to sum a row (where January 2004 is in column AV, Feb is in AU etc and the
more recent years are closer and closer to column A). So for year 2004 year
to date to May would sum AV to AR based on the number 5 inserted in cell A1
and June would sum AV to AQ based on the number 6 etc.

I am aware of OFFSET etc features but do not know the syntax to get it
right. Please help.

Sum column A:? where ? is a number in a cell somewhere
 

You didn't indicate which row(s) you need to add. If you need rows 5 and 6,
for example, and the number of months is in cell A1 you could use
=sum(offset($AW$5,0,-A1,2,A1))


"Murray" wrote:

I have a column for each month of the year and I need to compare this year
to date with last year to date and the previous year and the previous year
etc. I would like to enter a 1 to 12 in a cell somewhere that will allow me
to sum a row (where January 2004 is in column AV, Feb is in AU etc and the
more recent years are closer and closer to column A). So for year 2004 year
to date to May would sum AV to AR based on the number 5 inserted in cell A1
and June would sum AV to AQ based on the number 6 etc.

I am aware of OFFSET etc features but do not know the syntax to get it
right. Please help.

Sum column A:? where ? is a number in a cell somewhere
 

Not enough detail!

See if you can use this as a guide.

A1: =MONTH(NOW())

You need a column header across the row that identifies the years (or maybe
you already have dates???):

2005.............2006..............2007
J,F,M,A........J,F,M,A.........J,F,M,A
1,5,2,3..........3,2,1,7...........4,4,4,4

Where the year header is in row 3, the values to sum are in row 5:

=SUM(OFFSET(A5:J5,,MATCH(2006,A3:J3,0)-1,,A1))

That will give you the sum for the year 2006 for the number of months
referenced in cell A1.


--
Biff
Microsoft Excel MVP


"Murray" wrote in message
...
I have a column for each month of the year and I need to compare this year
to date with last year to date and the previous year and the previous year
etc. I would like to enter a 1 to 12 in a cell somewhere that will allow me
to sum a row (where January 2004 is in column AV, Feb is in AU etc and the
more recent years are closer and closer to column A). So for year 2004 year
to date to May would sum AV to AR based on the number 5 inserted in cell A1
and June would sum AV to AQ based on the number 6 etc.

I am aware of OFFSET etc features but do not know the syntax to get it
right. Please help.