Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Include trailing zeroes in calculation | Excel Discussion (Misc queries) | |||
days calculation | Excel Discussion (Misc queries) | |||
How to calculation no. of days (only working days) between two dat | Excel Discussion (Misc queries) | |||
Please Help With Days Elapsed And Days Remaining Calculation | Excel Worksheet Functions | |||
cALCULATION OF DAYS | Excel Worksheet Functions |