Date & Time
Sum the last 30 days (or rolling N days) with SUMIFS
Total revenue or orders inside a rolling 30-day window and avoid the classic RefDate - 30 vs RefDate - 29 off-by-one boundary mistake.
Watch the inclusive fencepost rule: RefDate - (N - 1)
If your reporting As-Of date is April 30 (day 30) and you include BOTH endpoints (>= StartDate and <= AsOfDate), an inclusive 30-calendar-day window starts on April 1 = AsOfDate - 29—NOT AsOfDate - 30 (which spans 31 calendar days from March 31 through April 30!).
Use a lower bound at midnight 29 days earlier and an exclusive upper bound at midnight after the As-Of day to include all timestamps on the final day.
| OrderDate (A) | Revenue (B) | Included in Apr 1–Apr 30 (30d)? |
|---|---|---|
| 2026-03-31 | 400 | No (30 days before the As-Of date — excluded) |
| 2026-04-01 | 1200 | Yes (Day 1 of 30-day window) |
| 2026-04-18 | 850 | Yes |
| 2026-04-30 | 950 | Yes (Day 30 -> Total = 3,000) |
| 2026-05-02 | 600 | No (future-dated — excluded) |
Rolling 30-day SUMIFS formula anchored to an As-Of cell
A fixed As-Of date defines the reporting window; preserve the source data too if the result must be reproducible.
=SUMIFS(B2:B6, A2:A6, ">=" & (INT(D2) - 29), A2:A6, "<" & (INT(D2)+1))Count orders in the same rolling 30-day window
Apply the exact same two-sided date bounds inside COUNTIFS.
=COUNTIFS(A2:A6, ">=" & (INT(D2) - 29), A2:A6, "<" & (INT(D2)+1))Inputs and interpretation
These are 30 whole calendar days, not a rolling 720-hour window. Use numeric timestamps in a consistent time zone and numeric amounts.
Try the example
Use the original values or change them to test your understanding.