Date Ranges in Criteria Functions

Real reports almost always ask about a time window: sales this quarter, orders last month, signups between two dates. The criteria functions you've learned — SUMIFS, COUNTIFS, AVERAGEIFS — handle this beautifully once you know how to express a date range.

The trick is that a date range is really two conditions on the same date column: on or after a start, and on or before an end.

Spreadsheets store dates as serial numbers — day 1 is January 1, 1900 (or 1899 in Sheets), and each later day adds one. That's why you can compare dates with > and < exactly like ordinary numbers.

So after January 1 simply means a serial number greater than that date's number. This is the key insight that makes date filtering work.

Suppose column A holds an Order Date and column C the Amount. To total sales from January 2024, you give column A twice: on or after Jan 1, and on or before Jan 31.

Wrap dates in the DATE(year, month, day) function so they're unambiguous across regional formats. The two conditions form an AND, capturing only the month's rows.

You might try typing ">=1/1/2024" directly. It often works, but it's fragile — the spreadsheet may read it as text or misinterpret the day and month order.

The robust pattern is ">="&DATE(2024,1,1). The DATE function builds a real serial number, and the & glues the operator to it. This is reliable in both Excel and Google Sheets, in any locale.

Hard-coded dates are fine for a fixed report, but a flexible one reads the start and end from cells. Put the start date in F1 and the end date in F2.

Now the period is driven by the sheet. Change F1 or F2 and every total recalculates. As always, join the operator to the cell with & — never put the cell name inside the quotes.

Sometimes you only need one boundary. Everything from a date onward uses a single greater-than-or-equal condition. Everything up to a date uses a single less-than-or-equal condition.

This counts all orders placed on or after the date in F1, with no upper limit — useful for "sales since launch" style metrics.

Date conditions mix freely with text and number conditions. To total East region sales within a date window, add the region pair alongside the two date pairs.

Order doesn't matter to the result — Excel evaluates all conditions as one big AND. Here three criteria pairs share the same average_range or sum_range.

To total an entire year, set the bounds to the first and last day of that year. Combine DATE for the start with the end of the period.

For a single month, use the first of the month as the lower bound and the first of the next month with a strict "<" as the upper bound — a neat way to avoid worrying about 28, 30, or 31 days.

For rolling reports, build the boundaries from TODAY(). To count orders in the last 30 days, the lower bound is today minus 30 and the upper bound is today.

Because TODAY() refreshes each day the sheet recalculates, the window slides forward automatically — no manual editing required.

If your date column actually stores date and time (like a timestamp), a row dated late on Jan 31 has a serial number slightly above the whole-day value of Jan 31. A "<="&DATE(2024,1,31) bound would exclude it.

The safe fix is the next-day strict-less-than pattern: "<"&DATE(2024,2,1) captures every moment within January, timestamps included.

The same date-range pattern works with AVERAGEIFS. To find the average order value within a window, give the amount column as the average_range and add the two date conditions on the date column.

Remember the empty-period trap: if no orders fall inside the dates, AVERAGEIFS returns #DIV/0!. Wrapping it in IFERROR keeps a time-filtered dashboard tidy when a period has no data.

Recall the robust way to compare against a date in criteria functions.

You can now filter SUMIFS, COUNTIFS, and AVERAGEIFS by time:

• A date range is two conditions on the same date column (>= start and <= end).
• Build dates with DATE(y,m,d) and attach operators using ">="&.
• For months, use a next-day strict upper bound ("<"&DATE(...)) to handle timestamps.
• Use TODAY() for rolling windows like the last 30 days.

That completes the multi-criteria IFS family.