Trailing 30 days sum calculation with excel

[Amy]

Hello,

I need help with a rolling/trailing 30 day formula in excel. I have my dates on one column and revenue in the next column. I used the formula below,

=SUM(OFFSET(B3,COUNTA(B:B)-30,):OFFSET(B3,COUNTA(B:B),))

and after the first 30 days were calculated, the following cells had zeroes and I have more than 90 days of data. I need to be able to add data from one day, subtract revenue from the first day of the month and have this updated with each new day.

Thanks.
Amy.

 

[Claus Busch]

Hi Amy.

Am Mon, 9 Jun 2014 13:50:09 -0700 (PDT) schrieb Amy:

=SUM(OFFSET(B3,COUNTA(B:B)-30,):OFFSET(B3,COUNTA(B:B),))

Your dates in column A, the values to sum in column B

If I understand you correctly then try:
=SUMIFS(B:B,A:A,">="&(A3-30),A:A,"<="&A3)
or only for the last 30 days:
=IF(A3<(MAX(A:A)-30),"",SUMIFS(B:B,A:A,">="&(A3-30),A:A,"<="&A3))


Regards
Claus B.