How to sum the last 30 days in Excel
Formula (Excel and Google Sheets)
=SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1)Use SUMIFS with a window that ends on the as-of date and starts 30 days earlier: =SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1).
- Result on the example
- $415 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
Dates are day numbers, so F1-30 is 30 days before the as-of date. Greater than that date and up to the as-of date is exactly 30 days: June 1 to June 30 here. May 31 is the 31st day back and is left out, and July 2 is after the as-of date.
=SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1)Result415
C2:C98 rows × 1 col: $85, $40, $120, $65, …A range: 8 cells in one column.
A2:A98 rows × 1 col: 5/12/2026, 5/31/2026, 6/1/2026, 6/8/2026, …A range: 8 cells in one column.
F16/30/2026The value in F1.
F1-3046173The - operator subtracts.
">"&F1-30">46173"The & operator joins as text.
"<="&F1"<=46203"The & operator joins as text.
SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1)415SUMIFS: Adds values that meet every condition.- sum_range
C2:C98 rows × 1 col: $85, $40, $120, $65, … - criteria_range1
A2:A98 rows × 1 col: 5/12/2026, 5/31/2026, 6/1/2026, 6/8/2026, … - criteria1
">"&F1-30">46173" - criteria_range2
A2:A98 rows × 1 col: 5/12/2026, 5/31/2026, 6/1/2026, 6/8/2026, … - criteria2
"<="&F1"<=46203"
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1). We ran it in Google Sheets mode on the same data and it returns $415.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((A2:A9>F1-30)*(A2:A9<=F1)*C2:C9) | $415 | The same window as two tests; easy to extend with conditions SUMIFS can't express. |
| =SUMIFS(C2:C9,A2:A9,">="&F1-30,A2:A9,"<="&F1) | $455Different result | Use >= if you mean "since the same day last month", which is 31 days including today. Includes May 31: 455. |
Common mistakes
- Leaving out the end condition also adds future-dated rows, like the July 2 refund here.
- With
>=at both ends the window is 31 days. Decide whether "last 30 days" includes today and count it.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum the last 30 days
F2, total the refunds in the 30 days ending on the date in F1, that day included.Questions
How do I always use today's date?
Replace F1 with TODAY(): =SUMIFS(C2:C9,A2:A9,">"&TODAY()-30,A2:A9,"<="&TODAY()).
How do I sum the last 12 months?
Use EDATE for the start: =SUMIFS(C2:C9,A2:A9,">"&EDATE(F1,-12),A2:A9,"<="&F1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.