Rolling 12 Mo. by date.

[tommyizzle]

Hello,
I am throwing in the towel and asking for help... I have been trying to find a formula that will track the sum for a range of cells and exclude any rows in which the date is more than one year old. Here is the data (I mocked up a visual aid using a table below):

• Dates will be entered in A4:A10000
• Raw data will be entered in columns B-F by row
• Ideally I would like each line to populate in column G by row and report the total to N1 (excluding anything older than 1 year)
• So far I have tried =SUMIF and =SUMIFS but in both cases it does not exclude any dates that are outside of one year old.
• I have also tried adding a running total column to report to N1 (which i would like to avoid if possible)

Any help is truly appreciated!

TIA - Thomas O.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Labels[/TD]
[TD]Label1[/TD]
[TD]Label2[/TD]
[TD]Label3[/TD]
[TD]Label4[/TD]
[TD]Label5[/TD]
[TD]Total[/TD]
[TD]Running Total (Optional)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rolling 12 Mo. Total[/TD]
[TD](Populates Here)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Date:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Example Line[/TD]
[TD]Ex.[/TD]
[TD]Ex.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ex. Sum[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11/20/2014[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0 (Because older than 12 Mo.)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01/13/2015[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]03/24/2015[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Like this?
Copy formulas in G4 and N4 down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Labels	Label1	Label2	Label3	Label4	Label5	Total	Running Total (Optional)					Rolling 12 Mo. Total	(Populates Here)
2	Date:												6
3	Example Line	Ex.	Ex.				Ex. Sum
4	11/20/2014		1	1			0							0
5	1/13/2015	1			1		2							2
6	3/24/2015		1			1	2							4
Sheet

 

[tommyizzle]

Thank you for the help! But I was looking to have N1 keep a running total of G4:G10000 excluding dates beyond a year. Any suggestions? If there's not, I can us the running total by line and then have that column report to N1.

 

[AhoyNC]

You should be able to use just a SUM function. As a row total in G gets past 1 year it will turn to 0.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Labels	Label1	Label2	Label3	Label4	Label5	Total	Running Total (Optional)					Rolling 12 Mo. Total	(Populates Here)
2	Date:													4
3	Example Line	Ex.	Ex.				Ex. Sum
4	11/20/2014		1	1			0
5	1/13/2015	1			1		2
6	3/24/2015		1			1	2
Sheet

 

[MARZIOTULLIO]

Hi

Try

N1 =SUM(IF(A4:A6>TODAY()-365,B4:F6)) control+shift+enter

 

[tommyizzle]

Actually I just copied the formula you had for the G4 cell down through G10000 and then used =SUM(G4:G10000) in N1 and that fixed it. Ran a few test and it's working great! Thanks again for the help, I was obviously trying to do it the hardest possible way!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.