Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum by variable column
I am having trouble wrapping my brain around this one.
I have a listing of columns for months (in reverse order). In each row is that months budget numbers. What I want to do is have a column that sums the year to date based on a value that is typed into a cell. Colunn U = Jan column T = Feb Column S = March etc So if I type a 1, I want the sum of row x columns U:U If I type a 3, I want the sum of row x columns S:U etc Thanks in advance for any help. Guy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum by variable column
If that number is in cell A1, for example, this formula will do what you
want (fill down): =SUM(OFFSET(J2,0,12-A1,1,A1)) "gr" wrote in message oups.com... I am having trouble wrapping my brain around this one. I have a listing of columns for months (in reverse order). In each row is that months budget numbers. What I want to do is have a column that sums the year to date based on a value that is typed into a cell. Colunn U = Jan column T = Feb Column S = March etc So if I type a 1, I want the sum of row x columns U:U If I type a 3, I want the sum of row x columns S:U etc Thanks in advance for any help. Guy |
#3
|
|||
|
|||
I put the months in row 2, columns J to U. With U2 = Jan
Cell A2 is reserved for the number of columns. Recommend you use data validation or some kind of drop-down list to restrict the user to 12 max. x values go in B3 to whatever The following formula was filled down from A3 to whatever =SUM(OFFSET($U$2,$B3,1,1,1):OFFSET($U$2,$B3,-$A$2+1,1,$A$2)) $U$2 = Jan $B3 = x = this row, column 2 $A$2 = # months in R1C1 notation: =SUM(OFFSET(R2C21,RC2,1,1,1):OFFSET(R2C21,RC2,-R2C1+1,1,R2C1)) hth... Quote:
|
#4
|
|||
|
|||
Guy's solution is better than mine...
I made a minor change to it: =SUM(OFFSET($U$2,$B3,-$A2+1,1,$A$2)) $U$2 = Jan $B3 = x value $A2 = # of months Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Can the column index in a cell address be made variable? | Excel Discussion (Misc queries) | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) |