COUNTIF Google Sheets Date Range: Formula, Examples & Tips

To count dates within a range in Google Sheets, use COUNTIFS with a start-date condition and an end-date condition. If the dates are in A2:A100 and you want all dates from January 1 through January 31, 2025, the formula is =COUNTIFS(A2:A100,">="&DATE(2025,1,1),A2:A100,"<="&DATE(2025,1,31)). The two comparisons include both boundary dates.

Why a date range uses COUNTIFS

COUNTIF tests a range against one condition, such as a particular date or all dates after a cutoff. A range bounded by both a beginning and an end needs two conditions: one says the date must be on or after the beginning, and the other says it must be on or before the end. In Google Sheets, COUNTIFS applies both conditions together and counts rows that satisfy them both.

The distinction is easy to miss because the function name sounds like a general-purpose count function. Use COUNTIF when one test is enough; use COUNTIFS when you need multiple tests, including a date interval. Each additional condition in COUNTIFS is supplied as a range-and-criterion pair.

For a valid date range, the two criteria ranges can be the same date column, as in the example, or different columns if you are also testing another field. The ranges in a COUNTIFS formula should have matching dimensions so each row's conditions are evaluated together.

How Google Sheets evaluates dates

Google Sheets stores actual dates as numeric values, which is why it can compare them chronologically. A cell that looks like a date may instead contain text. If a date is text rather than a date value, a comparison may not behave as expected even when the display looks convincing. Formatting changes how a value appears; it does not necessarily convert text into a real date.

In a criterion such as greater than or equal to a date, the comparison operator is text joined to the date value with &. The DATE function builds a date from year, month, and day numbers, avoiding ambiguity between formats such as day/month/year and month/day/year. In a US-style locale, for example, 03/04/2025 may be read differently than in a locale that puts the day first. Using DATE makes the intended calendar date explicit.

The date shown in a cell can use any suitable display format without changing the underlying value. To change the appearance, select the cells and choose Format > Number, then choose a date format. If the spreadsheet's locale differs, it can also affect how typed dates and formula argument separators are interpreted. The examples here use commas and work in a comma-separator locale.

Count dates between two dates in cells

Keeping the boundaries in cells makes a report easier to reuse. Suppose your dates are in A2:A100, the inclusive start date is in D1, and the inclusive end date is in E1. Enter actual date values in D1 and E1, then use this formula:

=COUNTIFS(A2:A100,">="&D1,A2:A100,"<="&E1)

This returns the number of dates in A2:A100 that are at least the value in D1 and no later than the value in E1. Change either boundary cell and the count updates automatically. To enter a date in a cell, type a date in a format recognized by the spreadsheet's locale, or use a formula such as =DATE(2025,1,1) in D1. With a real date value in E1, the criteria compare dates rather than text labels.

Before using a cell-based setup, check that the start date is not later than the end date. If the order is reversed, no ordinary date can meet both conditions, so the result will be zero. You can also add more pairs of criteria to the same COUNTIFS formula when the question has extra conditions.

Count dates in a particular month

For a calendar month, set the first boundary to its first day and the second to its last day. For example, with dates in A2:A100, this formula counts dates in February 2025:

=COUNTIFS(A2:A100,">="&DATE(2025,2,1),A2:A100,"<="&DATE(2025,2,28))

For months with different lengths, the end day must be adjusted. February may have 28 or 29 days, while April has 30. A convenient alternative for data that may include times is to use the first day of the month as an inclusive lower boundary and the first day of the next month as an exclusive upper boundary. That approach avoids calculating the last calendar day.

With dates or timestamps in A2:A100, this formula counts everything from February 1, 2025, up to but not including March 1, 2025:

=COUNTIFS(A2:A100,">="&DATE(2025,2,1),A2:A100,"<"&DATE(2025,3,1))

This pattern works for any month: use the month's first day as the lower bound and the next month's first day as the exclusive upper bound. It includes every time on the last day of the month without needing a special end-of-day time.

Include timestamps and the whole end date

A date-time value includes both a date and a time. A value recorded on January 31 at 3:00 p.m. is later than the date-only value for January 31, which represents midnight at the start of that day. Therefore, a condition of less than or equal to the end date can omit later timestamps on that same date.

If A2:A100 contains timestamps, B1 contains the start date, and C1 contains the end date, use a less-than comparison against the day after C1:

=COUNTIFS(A2:A100,">="&B1,A2:A100,"<"&C1+1)

This counts timestamps on the end date as well as earlier dates, while excluding midnight at the start of the following day. It assumes B1 and C1 contain date values, not text. If the data consists only of date values with no times, the inclusive less-than-or-equal formula is also appropriate.

Add another condition, such as a category

A date count often needs a second filter: for example, the number of completed orders in a period. Suppose dates are in A2:A100, order statuses are in B2:B100, and you want completed orders between the dates in D1 and E1. The formula is:

=COUNTIFS(A2:A100,">="&D1,A2:A100,"<="&E1,B2:B100,"Completed")

The formula counts a row only when its date meets both boundaries and its status is exactly Completed. The date criteria ranges and the status criteria range cover corresponding rows, so the conditions are checked record by record. Replace the status text with the category used in your sheet, matching its spelling and spacing.

For large sheets, use similarly sized ranges rather than pairing a short range such as A2:A100 with a full-column range such as B:B. Matching ranges make the intended row-by-row logic clear and can also avoid unnecessary work.

Common date-range errors and how to fix them

The result is zero even though dates appear in the range. Check whether the entries are real dates or text. Select a suspect cell and look for evidence that it is being treated as text, such as inconsistent alignment or failure to respond to date formatting. Re-enter or convert the values into dates before relying on date comparisons.
The first or last day is missing. For a fully inclusive interval, use >= for the start and <= for the end. If timestamps are present, use the first day after the end date as an exclusive upper bound instead.
The formula reports a parse error. Check that the quotation marks and parentheses are balanced and that the formula uses the argument separator required by the spreadsheet's locale. The examples use commas; some locales use semicolons between function arguments. Do not change the comparison operators inside the quoted criteria.
The count changes unexpectedly after formatting. Number formatting affects appearance, not the underlying date value. If the values were imported as text, choosing a date display format alone may not fix them. Convert the values, then verify the count again.
Dates seem to shift by a day. Check the spreadsheet's locale and the format used to enter or import dates. Ambiguous text dates are a frequent cause. Constructing boundaries with DATE(year,month,day) avoids that ambiguity in the formula.

COUNTIF versus COUNTIFS and other options

Use COUNTIF for a single date condition. For instance, with dates in A2:A100, the following counts dates on or after January 1, 2025, without setting an upper limit:

=COUNTIF(A2:A100,">="&DATE(2025,1,1))

For a bounded period, COUNTIFS is the clearer choice because both boundaries are visible in one formula. It also avoids manually filtering the sheet just to obtain a total. If you want to inspect the matching records rather than just count them, Google Sheets also has the FILTER function, which returns rows that meet conditions. A count formula is usually simpler when only the total is needed; filtering is useful when you need to review or work with the matching rows.

Google Sheets and Excel both provide COUNTIF and COUNTIFS, and the basic formulas shown here are familiar in both. This page's menu directions and locale notes are for Google Sheets in a web browser; the menus and some regional settings can differ in Excel. In Sheets, use Format > Number to adjust display formatting, not to assume that text has been converted into a date.

Frequently Asked Questions

Can COUNTIF count dates between two dates?

For two boundaries, use COUNTIFS, which can apply a start and end condition to the date range. COUNTIF supports one condition at a time, such as dates after a particular date.

Should the date range include the start and end dates?

Usually, yes. Use greater than or equal to for the start and less than or equal to for the end when the cells contain date-only values. If they contain timestamps, use a less-than condition for the day after the end date so every time on the end date is included.

Why does my date-range formula return zero?

The most common cause is that one or more apparent dates are text rather than numeric date values. Also check the boundary cells, the criteria operators, and whether timestamps make the end boundary too early in the day.

Can I use cell references for the date criteria?

Yes. Put the start and end dates in separate cells and join each comparison operator to its corresponding cell reference with &. This makes the count easy to update without editing the formula.

How do I count a month when the data includes times?

Use the month's first day as the inclusive lower boundary and the first day of the next month as an exclusive upper boundary. That includes all timestamps in the month and excludes the next month, regardless of the time recorded on the final day.

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.