Sum the Last 30 Days in Excel With SUMIFS and TODAY

To sum the last 30 days in Excel, use SUMIFS with two date conditions: one for the start of the window, TODAY()-29, and one for the end, TODAY(). The total moves forward by itself every day, so a "last 30 days sales" figure on a dashboard never needs editing. This guide shows the formula, explains why it uses 29 and not 30, and covers the fixes when the total comes out wrong.

How to sum the last 30 days with SUMIFS and TODAY#

SUMIFS adds the numbers in one column when the matching cells in other columns meet every condition you give it. For a rolling 30-day total, both conditions test the same date column:

=SUMIFS(C2:C100,A2:A100,">="&TODAY()-29,A2:A100,"<="&TODAY())

TODAY() returns today's date with no time. Excel stores dates as whole numbers (one per day), so TODAY()-29 is simply the date 29 days earlier. The & joins the operator ">=" to that date, which builds a condition such as ">=46297" that SUMIFS can read.

The examples below use this sales table, with a fixed report date of 31 Oct 2026 in F1 so the results don't change:

=SUMIFS(C2:C8,A2:A8,">="&F1-29,A2:A8,"<="&F1)

The window runs from 2 Oct to 31 Oct, so the result is 275 + 520 + 180 + 610 = 1,585. The 28 Sep and 1 Oct sales are too old, and the 3 Nov sale is after the report date. That end condition matters: without it, future-dated rows such as scheduled orders would sneak into the total.

Last 30 days: should today count?#

"The last 30 days" usually means today plus the 29 days before it, which is why the formula subtracts 29. Count it on your fingers: 2 Oct to 31 Oct is 30 days. Subtracting 30 would give 31 days.

Some reports want the 30 full days before today, because today's sales are not finished yet. Then move both ends back by one and use "less than" for the end:

=SUMIFS(C2:C8,A2:A8,">="&F1-30,A2:A8,"<"&F1)

With 31 Oct as the report date, this covers 1 Oct to 30 Oct and returns 1,275 (300 + 275 + 520 + 180). Neither version is "right": pick the one your team means, and write it in the report label, for example "Last 30 days incl. today".

If your date column holds times as well as dates (for example 31 Oct 2026 14:20), "<="&TODAY() drops today's rows, because 31 Oct at 14:20 is later than 31 Oct at midnight. Use "<"&TODAY()+1 as the end condition instead. The guide on removing the time from a date explains why.

Use a report date cell instead of TODAY#

For a report that must show the same number next week, don't build TODAY() into the formula. Put the report date in a cell and point the formula at it:

1. In F1, type the report date, or =TODAY() if you do want it to move.
2. In F2, type =SUMIFS(C2:C8,A2:A8,">="&F1-29,A2:A8,"<="&F1).

Now anyone can change F1 to see any 30-day window, and the formula never needs editing. This is also easier to check: the reader can see which date the total is based on. The same "date in a cell" idea drives month-to-date totals and year-to-date totals. For more on how TODAY() recalculates, see the guide to the TODAY function and the TODAY function reference.

Sum the last 30 days for one customer or category#

SUMIFS takes more conditions in pairs: a range, then its criteria. To total one customer's last 30 days, add a third pair:

=SUMIFS(C2:C8,B2:B8,"Bluebird Ltd",A2:A8,">="&F1-29,A2:A8,"<="&F1)

The result is 1,130 (520 + 610). Put the customer name in a cell, such as H2, and use B2:B8,H2 instead, then fill the formula down a list of customers to get a 30-day total for each one. Lock the ranges with $ first ($C$2:$C$8) so they don't move as you fill.

The SUMIFS function guide lists every argument, and the article on summing between two dates covers fixed start and end dates.

Count, average and compare the previous 30 days#

The same two date conditions work in COUNTIFS and AVERAGEIFS, so you can build a full 30-day summary from one pattern:

The last row lets you compare this month's rolling total with the one before: 1,585 against 750. For more counting patterns, read COUNTIFS with a date range.

Why is the last-30-days total wrong or 0?#

A SUMIFS date formula returns 0 or a strange total for a handful of reasons. Check these in order:

1. The whole condition is inside quotes. ">=TODAY()-29" is just text to Excel and matches nothing. Keep only the operator in quotes and join with &: ">="&TODAY()-29.
2. The dates are text. Dates imported from another system often look right but sit on the left of the cell. SUMIFS can't compare them to a date. Convert them first; converting text to dates shows how.
3. The ranges are swapped or different sizes. In SUMIFS the sum range is first. All ranges must be the same size, or Excel shows #VALUE!.
4. Dates include times. Today's rows drop out with "<="&TODAY(). Use "<"&TODAY()+1.
5. The window is off by one. A total that looks one day too long or short usually means 30 where you wanted 29, or the other way round.

Practise it#

Date criteria are a common job-test task because they combine three skills: SUMIFS, operators in quotes and the & join. Practise dates in criteria in a live spreadsheet: you type the formula and it checks it instantly. Then build multi-condition totals in the COUNTIFS and SUMIFS lesson, or try the exercise on this page first.