COUNTIFS Formula for Date Range in Excel With Examples

Formulas & functions · Updated November 21, 2026

To count dates within a range in Excel, use COUNTIFS with a start-date criterion and an end-date criterion. For a range that includes all of January 2025, for example, the formula is =COUNTIFS(A2:A8,">="&DATE(2025,1,1),A2:A8,"<"&DATE(2025,2,1)); it counts dates from January 1 up to, but not including, February 1.

The basic COUNTIFS date-range formula

Assume cells A2:A8 contain these entries: January 1, 8, 15, and 31, 2025; February 1, 2025; January 15, 2025 at 2:30 PM; and one blank cell. The formula above returns 5 because five entries fall in January. The time on the January 15 entry does not change its position within the month.

COUNTIFS checks one or more ranges against corresponding criteria and counts rows or cells where every condition is true. For a date range, give it the date column twice: once to test the lower boundary and once to test the upper boundary. The criteria combine comparison operators with dates, so the formula means “on or after January 1 and before February 1.”

Using the first day of the following month as an exclusive upper limit is generally the safest pattern. It includes every time on the last day of the period, not just dates recorded at midnight. For a different period, replace the two boundary dates with the dates you need.

How COUNTIFS evaluates date criteria

Excel stores valid dates as serial numbers, with times represented as fractions of a day. A cell displayed as January 15 may contain a date value, a date-and-time value, or text that merely looks like a date. COUNTIFS compares the underlying values, not their display format. Changing a cell’s number format can alter how it looks, but it does not convert text into a real date.

Comparison operators in a criteria argument must be supplied as text, such as ">=" or "<". When a boundary comes from a cell or a date-building function, join the operator to that value with &. The operator and date are then evaluated together as one criterion. This is why a criterion is not written as a comparison outside the function.

The DATE function creates a date from year, month, and day numbers. It avoids ambiguity between regional date formats, where a typed date such as 3/4/2025 might be interpreted differently. In the example, DATE(2025,2,1) unambiguously represents February 1, 2025.

Inclusive end dates and exclusive boundaries

If every value in the date column is a date without a time, an inclusive start and end test is also valid. With the same A2:A8 sample dates, this formula returns 5 for January 2025:

With January 1 in A2:A8 as the range’s first boundary and January 31 as its last boundary, use =COUNTIFS(A2:A8,">="&DATE(2025,1,1),A2:A8,"<="&DATE(2025,1,31)). It returns 5: four date-only January entries plus the January 15 timestamp.

That inclusive formula has a subtle limitation: DATE(2025,1,31) means January 31 at midnight. A value such as January 31 at 3:00 PM is greater than that boundary and would not be counted. To include any time on the final day, either use a boundary before the first day after the period or use a less-than condition for that next day. The exclusive-boundary pattern is less prone to this hidden omission.

For an interval that should exclude both endpoint dates, use a greater-than start condition and a less-than end condition. Decide whether each boundary is inclusive before writing the criteria; “between these dates” does not always specify whether the endpoints count.

Using start and end dates in worksheet cells

Cell references make a date count easy to reuse. Put the first date in B1 and the last date in C1, and keep the data dates in A2:A8. If B1 is January 1, 2025, and C1 is January 31, 2025, this formula returns 5:

With dates in A2:A8, the start date in B1, and the inclusive end date in C1, enter =COUNTIFS($A$2:$A$8,">="&B1,$A$2:$A$8,"<"&C1+1). For the sample data, the result is 5 because the formula counts everything from the start of January 1 through the end of January 31, including times during those days.

The dollar signs lock the data range if you copy the formula elsewhere; B1 and C1 remain relative references so they can change as the formula is copied. Format B1 and C1 as dates so the inputs are readable, and make sure they contain actual Excel date values. If C1 is already the first excluded date rather than the last included date, do not add 1; use it directly as the less-than boundary.

Counting one day when records contain times

A common related need is counting records on a particular calendar day. An equality test against a date can miss entries that include times, because a timestamp later in the day is not equal to midnight. Two criteria—greater than or equal to the day and less than the following day—cover the full 24-hour calendar date.

With dates and times in A2:A8 and the target date January 15, 2025, in B1, use =COUNTIFS(A2:A8,">="&B1,A2:A8,"<"&B1+1). The sample returns 2: one entry on January 15 at midnight and one at 2:30 PM. A blank cell and dates on other days do not meet both conditions.

This approach is also useful for daily reports when the source column records precise event times. It does not round timestamps or change the source data; it simply defines the day as a continuous interval from midnight up to, but not including, midnight on the next day.

Adding another condition, such as a category

COUNTIFS can count dates in a range while also requiring a matching department, status, region, or other field. Each additional condition uses another range and criterion. All criteria must be true for a record to count, and each criteria range must have the same dimensions as the date range.

Suppose A2:A8 contains the sample dates and B2:B8 contains regions, with East in B2, B4, B5, and B7, and West in B3 and B6. To count January records from East, use =COUNTIFS(A2:A8,">="&DATE(2025,1,1),A2:A8,"<"&DATE(2025,2,1),B2:B8,"East"). The result is 4: the January entries in rows 2, 4, 5, and 7 meet both the date and region conditions.

Criteria such as a status can be added in the same way, but the matching ranges must line up row by row. For example, a date range with seven rows cannot be paired with a six-row category range. A size mismatch can produce a #VALUE! error rather than a meaningful count.

Common problems and how to fix them

  • Dates are stored as text. A value that looks like a date may not be a numeric Excel date. Test a suspicious cell by changing its number format to General or by checking whether calculations treat it as a number. Convert text dates using an appropriate data-import or conversion method before relying on date comparisons.
  • The last day is unexpectedly missing. If records can contain times, a criterion less than or equal to the end date often excludes timestamps after midnight on that date. Use the day after the intended end as an exclusive upper limit instead.
  • The formula returns zero. Check that the source dates are real dates, the boundary cells are valid dates, and the start is not later than the end. Also check that the formula refers to the intended column and that there are no unexpected spaces or text values.
  • Range sizes do not match. Every criteria range in a single COUNTIFS formula should cover the same number of rows and columns. Keep the ranges aligned when expanding a formula.
  • A typed date changes meaning. Date interpretation can depend on regional settings. Using DATE(year,month,day) or clearly entered date cells reduces ambiguity.
  • The result changes after copying. Lock source ranges with dollar signs when copying a formula, and leave boundary references unlocked only if they should move. Confirm that the copied range still covers all relevant records.

COUNTIFS compared with other counting approaches

Use COUNTIFS when the task is to count records that meet multiple conditions, such as a date interval and a region. It is clearer than a long expression that adds separate counts, and it can be extended with more criteria. For a single criterion, COUNTIF is a simpler option, but it does not handle paired start and end tests in one call.

Filtering the data and reading the visible row count can help with a one-time inspection, but it is less suitable for a repeatable summary because the result depends on the active filter and manual steps. A PivotTable is useful when the goal is to summarize many dates by month, category, or other fields. For a single date-range count that should update when the boundaries change, COUNTIFS is usually the direct choice.

In Microsoft 365 for Windows, enter the formula in a worksheet cell and press Enter. Excel for the web uses the same function and formula pattern. On Mac, COUNTIFS works with the same syntax; menu and keyboard details can differ, but no alternate date-range formula is needed.

Frequently Asked Questions

Can COUNTIFS count dates between two dates?
Yes. Use the date range twice, with a lower-bound criterion and an upper-bound criterion. For periods that may contain timestamps, make the upper bound exclusive by using the day after the final date.

How do I count dates for a whole month?
Use the first day of the month as the inclusive lower bound and the first day of the next month as the exclusive upper bound. This works for months of different lengths and includes timestamps on the last day.

Does COUNTIFS include the start and end dates?
It includes whichever boundaries your operators specify. Greater than or equal to includes the start; less than or equal to includes an exact end value. For a full final calendar day with timestamps, use a less-than test against the following day instead.

Why does my date-range COUNTIFS formula return zero?
The most common causes are text dates, incorrect boundary references, or a start date later than the end date. Confirm that the source cells and boundary cells contain real date values and that the criteria ranges cover the intended rows.

Can I count dates in a range and match a status too?
Yes. Add another criteria-range and criterion pair for the status column. Keep that range aligned in size and row position with the date range; records count only when every condition is met.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.