Trailing 30 days sum calculation with excel
 

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.

Trailing 30 days sum calculation with excel
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional