summing the relevant columns

[Guest]

Hi,

i have a date that changes every month in cell A1, ie, 31/8/07

in row 2, i have the months of the year, ie,

1/1/07 1/2/07 1/3/07 .... 1/8/07.... 1/12/07 (Note that i have to put
1st day of the month in order to do some calculations)

in the last used column, i have to sum the amounts for each row from 1/1/07
to the month as shown in cell A1, ie, assuming apr to jul are NIL, this
column shld read, for the below example, 70. Then next month, when A1 is
changed to 30/9/07, this column should read 80 automatically w/o having the
user to change the formula in this column. Is there any way to do this?

Thanks!

1/1/07 1/2/07 1/3/07 .... 1/8/07 1/9/07.... 1/12/07
10 20 10 30 10

Val

 

[Guest]

So you're entering the first of every month in every column starting cell A2
and ending cell L2, and the values are right below cells A3-L3. Right?
Paste this:
=SUM(A3:OFFSET(A3,0,MONTH(A1)-1))

 

[Guest]

Hi,

it works! Thanks!

Val

So you're entering the first of every month in every column starting cell A2
and ending cell L2, and the values are right below cells A3-L3. Right?
Paste this:
=SUM(A3:OFFSET(A3,0,MONTH(A1)-1))