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.

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:

3

Example Line

Ex.

Ex.

Ex. Sum

4

11/20/2014

1

1

0 (Because older than 12 Mo.)

5

01/13/2015

1

1

2

2

6

03/24/2015

1

1

2

4

7

<tbody>
</tbody>

 

[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.