Google Sheets Date Range Formula: Count, Filter, and Sum

In Google Sheets, use COUNTIFS to count dates within a range, SUMIFS to total matching values, or FILTER to return matching rows. For dates in A2:A and the start and end dates in E1 and F1, an inclusive count is =COUNTIFS(A2:A,">="&E1,A2:A,"<="&F1).

What a date range formula does

A date range formula identifies values on or between two dates. The right formula depends on what you want to do with the matching records: count them, add their amounts, display the rows, or create a continuous list of dates. A date range is not a special data type or one single Google Sheets function; it is a pair of boundary conditions used with the function that suits the task.

For example, a sales sheet might have transaction dates in column A and amounts in column B. Cells E1 and F1 can hold the first and last dates to include. Changing either boundary updates the calculation, so you can reuse the same formula for a week, month, quarter, or custom period.

The examples below use comma separators, as in a spreadsheet with a US-style locale. Google Sheets may use semicolons instead in some locales. Enter dates as actual date values, not text that merely looks like a date.

Choose the result you need

Pick a function based on the intended result rather than trying to make one formula do everything. Common choices include:

COUNTIFS counts records whose dates satisfy both boundaries.
SUMIFS adds values from rows whose dates are within the boundaries.
FILTER returns the matching records, which you can inspect or use elsewhere.
SEQUENCE creates a consecutive list of dates, useful for a calendar or report axis.

These approaches treat the start and end as inclusive when using “greater than or equal to” and “less than or equal to.” That is usually what people mean by “between January 1 and January 31.” If your data includes times as well as dates, however, the end boundary often needs a small adjustment, explained below.

Count dates between two boundaries

Assume A2:A5 contains January 1, January 8, January 31, and February 1, 2025, and E1 and F1 contain January 1 and January 31, 2025. This counts dates on or after E1 and on or before F1:

With dates in A2:A5 and the start and end dates in E1 and F1, use:

=COUNTIFS(A2:A,">="&E1,A2:A,"<="&F1)

The result is 3: January 1, January 8, and January 31 qualify, while February 1 does not. The comparisons are joined as criteria strings: the operator is written in quotation marks and joined to the cell reference with an ampersand. Using COUNTIFS also lets you add other conditions, such as counting only rows whose category is “Online.”

For a data set with a header, starting at row 2 keeps the header out of the calculation. Open-ended ranges such as A2:A can be convenient as new rows are added; a bounded range such as A2:A5000 may be preferable in a very large sheet. Keep the date and criteria ranges the same size when using multiple conditions.

Sum values for a date range

To total sales or expenses recorded during a period, use SUMIFS. Suppose A2:A5 contains the four dates above and B2:B5 contains 12, 8, 20, and 5. Put the start and end dates in E1 and F1. The formula sums only the amounts attached to dates in the selected period:

With dates in A2:A5, amounts in B2:B5, and boundaries in E1 and F1, use:

=SUMIFS(B2:B,A2:A,">="&E1,A2:A,"<="&F1)

The result is 40, because 12 + 8 + 20 are associated with the three dates in January, and the February 1 amount of 5 is excluded. The first argument is the range to sum; the remaining pairs specify a range and its condition. Add another range-and-condition pair if you also need to limit the total by a department or status.

Return matching rows with FILTER

If you need to see the transactions themselves, FILTER returns rows that satisfy the date conditions. In this example, the returned result includes both columns A and B for January 1, January 8, and January 31, and excludes the February 1 row.

With records in A2:B5, dates in A2:A5, and the range boundaries in E1 and F1, use:

=FILTER(A2:B,A2:A>=E1,A2:A<=F1)

The matching rows spill into the cells below and to the right of the formula, so leave that area clear. If no records meet the criteria, FILTER returns an error indicating that no matches were found. You can handle that case with a message if it is useful for your report.

Generate every date in a range

Sometimes “date range” means a continuous list of dates rather than a selection of existing records. SEQUENCE can generate one date per row. If E1 is January 1, 2025, and F1 is January 5, 2025, the formula returns five consecutive date values, starting with E1.

With the start date in E1 and end date in F1, where the end is on or after the start, use:

=SEQUENCE(F1-E1+1,1,E1,1)

The “plus one” includes both boundary dates: the difference between January 5 and January 1 is four days, but the inclusive list has five entries. Format the output as dates using Format > Number > Date; otherwise, Sheets may display the underlying date serial numbers. If the end date precedes the start date, this setup does not describe a valid positive-length list, so check the inputs before relying on it.

Handle timestamps and end-of-day boundaries

A date-time value includes a time portion, even if the cell is formatted to show only the date. That matters when the end boundary is January 31 at midnight: a record at 3 p.m. on January 31 is later than that exact value and will not pass a “less than or equal to January 31” test. This is a common reason a formula appears to omit records from its final day.

For timestamped entries, a dependable pattern is to include everything from the start date up to, but not including, the day after the end date. If dates or timestamps are in A2:A and the boundaries are E1 and F1, count records this way:

=COUNTIFS(A2:A,">="&E1,A2:A,"<"&F1+1)

The strict “less than” comparison includes all times on the end date but excludes midnight at the start of the following day. Apply the same logic to sums or filters by replacing the upper-bound condition with a less-than comparison against the end date plus one. This method is more robust than trying to specify the final second of a day.

Make dates and boundaries reliable

Google Sheets stores recognized dates as numeric values, which allows date comparisons and arithmetic. The displayed format can differ from the stored value: a cell can show “Jan 1” while containing a date value with a year, or display only a date while storing a time too. Formatting changes appearance; it does not turn text into a valid date.

Enter boundaries in cells and format them as dates using Format > Number > Date.
Check that the source column contains real date values. Text imported from another system may look correct but fail date comparisons.
Keep start and end dates in separate cells so readers can see and change the reporting period.
For repeatable reporting, consider adding date validation through Data > Data validation so boundary cells are less likely to contain unintended entries.

Spreadsheet locale affects how dates are interpreted and how formulas are entered. For example, day/month order varies by locale, and some locales use semicolons as formula argument separators. If an entered date is ambiguous, choose it from the date picker or use an unambiguous date entry for your locale, then confirm that Sheets recognizes it as a date.

Common errors and useful checks

If a formula returns zero unexpectedly, first check whether the source dates are actual date values rather than text. Also verify that the start date is not later than the end date, and confirm that the cells named in the formula are the cells containing the intended boundaries. A formula can be syntactically correct while referencing the wrong column or an empty boundary cell.

If the final day is missing, inspect whether the source entries contain times. Use the exclusive next-day boundary described above for timestamp data. If a generated date list displays numbers, apply a date number format. If a filter reports no matches, inspect a few source cells and compare their underlying values—not just their visible formatting—with the boundaries.

To visually highlight dates in a range, select the date cells and open Format > Conditional formatting. A custom formula can compare each row with the boundary cells. With the rule applied to A2:A, dates in column A, and boundaries in E1 and F1, enter:

=AND($A2>=$E$1,$A2<=$F$1)

Choose the formatting style in the conditional-formatting panel. The dollar signs keep the boundary references fixed as the rule evaluates other rows, while the row number in A2 adjusts for each row. For data containing timestamps, use an upper test of less than the end date plus one instead.

How Google Sheets compares with Excel

The basic date logic is familiar in Excel and Google Sheets: both can use functions such as COUNTIFS and SUMIFS with date criteria. This answer is for Google Sheets in a web browser. Menu labels, locale-specific separators, and some function behavior or availability can differ between spreadsheet programs, so do not assume that every Sheets instruction describes an Excel menu. When sharing formulas, confirm that the recipient’s locale and application interpret dates and separators as intended.

Frequently Asked Questions

How do I count dates between two dates in Google Sheets?
Put the start and end dates in cells, then use COUNTIFS with a greater-than-or-equal criterion for the start and a less-than-or-equal criterion for the end. Use a less-than end-date-plus-one criterion if the source values include times.

Does a Google Sheets date range include the start and end dates?
It depends on the comparison operators. Using “greater than or equal to” and “less than or equal to” includes both dates. A “less than” upper boundary excludes the boundary itself, which is useful when that boundary is the day after the period you want.

How do I sum amounts between dates in Google Sheets?
Use SUMIFS, setting the amount column as the sum range and applying the start and end conditions to the date column. Make sure the amount and date rows line up.

Why does my date range formula return zero?
Common causes include dates stored as text, reversed or incorrect boundary cells, and dates that include times. Check the underlying cell values and ensure the date column and boundary cells use recognized dates.

How can I create a list of dates between two dates?
Use SEQUENCE with a row count equal to the end date minus the start date plus one, starting at the start date with a step of one. Format the resulting cells as dates so the serial values display as calendar dates.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.