Trailing 30 days sum calculation with excel

  • Thread starter Thread starter Amy
  • Start date Start date
A

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.
 
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),))
Click to expand...

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.
 
Back
Top