📎 This article includes 1 downloadable practice file ↓

Daily figures jump around; managers want to see the trend. “Sales in the last 7 days”, “a 7-day moving average”, “trailing 12 months” all smooth the noise. They’re SUMIFS and AVERAGE with date windows.

Last 7 days including today

=SUMIFS(I2:I201, A2:A201, ">=" & TODAY()-6, A2:A201, "<=" & TODAY())

Today and the six days before it = 7 days. For a fixed report date, replace TODAY() with a cell such as $Q$1.

Last 30 days, count and average

=COUNTIFS(A2:A201, ">=" & TODAY()-29, A2:A201, "<=" & TODAY())
=AVERAGEIFS(I2:I201, A2:A201, ">=" & TODAY()-29, A2:A201, "<=" & TODAY())

7-day moving average down a daily list

If each row is one day (dates in A, totals in B) and rows are in date order, starting at row 8:

=AVERAGE(B2:B8)          ' copy down: B3:B9, B4:B10, …

When days can be missing or several orders share a day, use dates instead of row counts:

=AVERAGEIFS($B$2:$B$400, $A$2:$A$400, ">" & A8-7, $A$2:$A$400, "<=" & A8)

Trailing 12 months (TTM)

=SUMIFS(I:I, A:A, ">" & EDATE($Q$1, -12), A:A, "<=" & $Q$1)

EDATE handles month lengths and leap years properly; don’t subtract 365.

Rolling 3-month sum in a monthly table (365)

=MAP(SEQUENCE(ROWS(B2:B13)), LAMBDA(i, IF(i<3, "", SUM(INDEX(B2:B13, i-2):INDEX(B2:B13, i)))))

INDEX-based ranges avoid OFFSET, which recalculates on every change and slows big workbooks.

Where people go wrong

• Off-by-one windows — “last 7 days” is TODAY()-6 to TODAY(). Decide whether today counts.
• Dates with times — 03-Oct 18:00 is greater than 03-Oct; use "<" & TODAY()+1 as the upper bound.
• Row-based averages on gappy data — 7 rows aren’t 7 days if Sundays are missing.
• TODAY() in reports you send — the numbers change when the reader opens the file.

Practice

Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.

More: all formula combos · Excel function course.