Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate a 30-day rolling sum in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMIFS(B2:B6,A2:A6,">="&(DATE(2026,9,16)-29),A2:A6,"<"&(DATE(2026,9,16)+1))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Date serial | Amount |
| 46251 | 999 |
| 46252 | 100 |
| 46266 | 200 |
| 46281.5 | 300 |
| 46282 | 900 |
=SUMIFS(B2:B6,A2:A6,">="&(DATE(2026,9,16)-29),A2:A6,"<"&(DATE(2026,9,16)+1))→600
Both expressions are checked against an independently calculated expected result in HyperFormula. This is a sample execution check, not native Excel or Google Sheets certification.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Paste the sample at A1. The numeric dates use the modern Excel 1900/Google Sheets date system; format column A as Date/time to inspect them.
- 246251 is 17 August 2026, 46252 is 18 August, 46266 is 1 September, 46281.5 is noon on 16 September, and 46282 is 17 September.
- 3Put the formula outside A1:B6. The lower bound includes 18 August; the upper bound excludes 17 September and later.
- 4Check the arithmetic: 100 + 200 + 300 = 600. The 999 and 900 rows are outside the window.
- 5Replace DATE(2026,9,16) with a cell containing your reporting date and extend both ranges together.
Tips
- This is 30 calendar days, not the most recent 30 rows or one calendar month.
- Using < reporting-date + 1 includes timestamps throughout the last day. Keep one reporting timezone and numeric dates; do not compare unparsed date text.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why subtract 29 rather than 30?
The reporting day counts as one of the 30 days. Subtracting 30 and including both endpoints would cover 31 calendar dates.
Will future transactions count?
No. The upper-bound condition excludes timestamps at or after the next day.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.
Sources and compatibility
Reviewed 2026-09-16