# How to sum the last 30 days in Excel

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)`.

**Excel:** `=SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Reason | Refund |  | As of | 2026-06-30 |
| 2026-05-12 | Damaged | 85 |  | Last 30 days |  |
| 2026-05-31 | Late | 40 |  |  |  |
| 2026-06-01 | Damaged | 120 |  |  |  |
| 2026-06-08 | Wrong item | 65 |  |  |  |
| 2026-06-15 | Late | 25 |  |  |  |
| 2026-06-22 | Damaged | 150 |  |  |  |
| 2026-06-30 | Wrong item | 55 |  |  |  |
| 2026-07-02 | Late | 30 |  |  |  |

Formula in F2: `=SUMIFS(C2:C9,A2:A9,">"&F1-30,A2:A9,"<="&F1)`

Result: $415

## 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.

## Other ways

- `=SUMPRODUCT((A2:A9>F1-30)*(A2:A9<=F1)*C2:C9)`: 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)`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-the-last-30-days