sum over a changing range

[almagg]

so the fiscal year is Oct - Sep
as new data appears each month i want the YTD column to update.
right now i have formulas extended thru all the months e.g., Aug & Sep columns have values in them but they are there because of the formulas.

so i want the YTD to be summing Oct - Jul

ColC..........ColD.....ColE..........................................ColM
YTD..........Oct.......Nov..........................................Jul
1000..........50........100..........................................75

i thought nof starting with a 'Match'

=MATCH("*"&TEXT(TODAY()-DAY(TODAY()),"mmm") &"*",D2:Q2,0)

which returns '10' the correct column for Jul.
but then i got stumped.

thanks

 

[wigi]

The OFFSET function can be used to for a summing over a range that is expanding and contracting. One of its arguments will be the number of columns - it's the last argument in fact.

 

[almagg]

got it.

=SUM(OFFSET(D3,0,0,1,MATCH(TEXT(TODAY()-DAY(TODAY()),"mmm"),D2:Q2,0)))

thanks