Sum values for YTD

[six4680]

This one's probably simple, but...
I have a spreadsheet that contains dates in Column A formatted as dates, entered as 3/19/2004. In column B and subsequent columns, I have numerical data that is summed at the bottom. The current formula looks like this... =SUM(B91:B143). It totals the data for 52 weeks since data is entered each week. The question is, is there a way to have this automatically adjust the SUM function to include only the last 52 weeks/cells, rather than adjusting the function parameters each week? Any help is appreciated.

 

[santeria]

Can you Give an example of what your sheet is at the moment ???

 

[2rrs]

Something like this perhaps:

=SUM(OFFSET(B1,MATCH(9.99999999999999E+307,B:B)-1,0,-52,1))

 

[Aladin Akyurek]

...In column B and subsequent columns, I have numerical data that is summed at the bottom...

Since you do the required summing in the same column as the column that houses the relevant figures, we can't alas use the Offset formula 2rrs posted...

Define LastN as referring to 52 via the option Insert|Name|Define.

In B144 enter:

=SUM(INDEX(B:B,ROW()-1):INDEX(B:B,ROW()-1-MIN(LastN-1,COUNT(B1:INDEX(B:B,ROW()-1)))))

 

[six4680]

Thanks all. That last one seems to have done it.