Sum values for YTD

six4680

New Member
Joined
Mar 22, 2004
Messages
2
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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
Can you Give an example of what your sheet is at the moment ???

(y)
 

2rrs

Active Member
Joined
Dec 25, 2002
Messages
346
Something like this perhaps:

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
six4680 said:
...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)))))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top