Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding the same number to each cell in a column | New Users to Excel | |||
Adding number to each cell in a column | Excel Discussion (Misc queries) | |||
Display row & column number in cell | Excel Worksheet Functions | |||
how do you add a new number to each cell in a column | New Users to Excel | |||
Hide Cell Row and column number | Excel Discussion (Misc queries) |