Excel COUNTIFS Date Range Not Working? Fix the Criteria

If Excel COUNTIFS is not counting dates in a range, first make sure the cells contain real Excel dates, then use criteria joined to dates with &. For example, with dates in B2:B100, count January 2025 using =COUNTIFS(B2:B100,">="&DATE(2025,1,1),B2:B100,"<"&DATE(2025,2,1)); the exclusive February 1 boundary also includes every time on January 31.

How COUNTIFS evaluates a date range

COUNTIFS checks one or more ranges against paired criteria and counts rows that meet every criterion. A date range is usually expressed as two conditions on the same date column: greater than or equal to the start date, and less than the day after the end date. This is an AND test, not an either-or test.

Excel stores recognized dates as serial numbers, with a time represented as a fraction of a day. That is why a displayed date can look correct while its underlying value contains a time, or why a date-looking text string fails a numeric date comparison. Cell formatting changes how a value appears; it does not convert text into a date.

In the opening formula, the first condition includes January 1 and later dates, while the second stops before February 1. The two conditions use the same range, B2:B100, so each record is tested against both boundaries. COUNTIFS also supports conditions on other columns, such as a region or status, as long as the criteria ranges have matching dimensions.

Try a small example before changing the workbook

Suppose B2:B7 contains four real date-and-time values: January 1, 2025 at 9:00 a.m.; January 15 at noon; January 31 at 11:59 p.m.; and February 1 at midnight. B6 is blank, and B7 contains the text “Jan 20, 2025,” not an Excel date. With that layout, the following formula returns 3 because the first three numeric values fall in January and the text entry is not counted:

With the six sample entries in B2:B7, use: =COUNTIFS(B2:B7,">="&DATE(2025,1,1),B2:B7,"<"&DATE(2025,2,1)). The result is 3: January 1, January 15, and January 31 qualify; February 1 does not.

This simple test helps separate a formula problem from a data problem. If the formula works on clean sample dates but returns zero or too few matches in the original column, inspect the original values for text, time components, blanks, or dates outside the chosen boundaries.

Build criteria with DATE or date-cell references

A frequent syntax error is typing a date function inside quotation marks as if Excel should calculate it. Criteria such as ">=DATE(2025,1,1)" are text; COUNTIFS does not evaluate the DATE function inside that quoted string. Instead, put the comparison operator in quotes and join it to a real date value with the ampersand.

With the sample dates in B2:B7, the correct pattern is ">="&DATE(2025,1,1). DATE constructs a date from year, month, and day numbers, avoiding ambiguity between formats such as 1/2/2025, which can mean January 2 or February 1 depending on regional settings.

You can also store the boundaries in worksheet cells. With dates in B2:B7, the start date in E2 (January 1, 2025), and the final included date in F2 (January 31, 2025), use =COUNTIFS(B2:B7,">="&E2,B2:B7,"<"&(F2+1)). For the sample values, this returns 3. Adding one day to F2 and using a less-than condition includes any time during the final date without relying on a particular time of day.

Account for hidden times in date values

Time components are a common reason an apparently inclusive end date misses records. A cell formatted to show only a date might contain January 31 at 3:30 p.m. Internally, that value is later than midnight at the start of January 31, so a condition that requires the value to be less than or equal to January 31 at midnight will exclude it.

For calendar-day reporting, use a start-inclusive and next-day-exclusive range: greater than or equal to the first day, and less than the day after the final day. This pattern handles midnight, noon, and other times consistently. It also avoids guessing the last possible time value that Excel can store.

If you genuinely want to count only values recorded at exactly midnight, an end boundary at the date itself may be appropriate. But that is unusual for imported logs, orders, or appointments. Before changing the formula, temporarily display the cells with a date-and-time number format or inspect a representative value in the formula bar to see whether a time is present.

Check whether the cells are real dates or text

Imported CSV files, copied web data, and inconsistent regional date conventions can leave dates stored as text. Text may look like a date and may even sort in a date-like order, yet COUNTIFS date comparisons will not treat it like a numeric date. A quick check is to count the numeric entries in the date column.

With the sample values in B2:B7, enter =COUNT(B2:B7) in an unused cell. It returns 4 because four cells contain numeric date-and-time values; the blank and text date are not counted. If the count is unexpectedly low compared with the number of populated date cells, investigate whether some entries are text. COUNT also counts other numbers, so it is a diagnostic clue rather than proof that every value is a valid date.

To convert a consistent text-date column, make a copy of the data first. In Excel for Microsoft 365 on Windows, select the column and choose Data > Text to Columns. Choose Delimited, click Next twice, select Date, choose the order that matches the source data (such as MDY or DMY), and click Finish. Verify the result on several rows, especially dates where the day is 12 or lower, because those are easiest to interpret incorrectly. On Mac, the Text to Columns command is also on the Data tab, though the dialog layout can differ; Excel for the web may not offer the same full conversion workflow, so open the workbook in the desktop app if needed.

Do not convert a mixed column blindly. If some values are already real dates and others are text, or if the source uses multiple formats, identify the formats first. A wrong conversion can produce valid-looking but incorrect dates, which is harder to catch than a formula returning zero.

Check range sizes, blanks, and other criteria

Each criteria range in COUNTIFS must have the same number of rows and columns as the other criteria ranges. For example, pairing A2:A100 with B2:B99 can produce a range-size error. Keep all paired ranges aligned to the same records, and avoid including a header in one range but not another.

When the date is only one of several conditions, each extra condition narrows the result. For example, suppose A2:A6 contains North, South, North, North, and South, while B2:B6 contains January 3, January 9, February 1, January 31, and January 20, 2025. With the region labels in A2:A6 and dates in B2:B6, this formula returns 2: the North records dated January 3 and January 31 meet both tests.

With the five example regions in A2:A6 and their dates in B2:B6, use: =COUNTIFS(A2:A6,"North",B2:B6,">="&DATE(2025,1,1),B2:B6,"<"&DATE(2025,2,1)). The result is 2. If a date-only formula works but the multi-condition formula does not, check the labels for extra spaces or inconsistent wording, and verify that each range begins and ends on the same rows.

Also check whether blanks are meaningful. A blank cell is not a date in the requested period and should not count toward a date-range condition. A formula that displays an empty string can behave differently from a truly empty cell in other tests, but neither should be treated as a valid date without checking the underlying value.

Common formula and workbook problems

When the result is zero or clearly wrong, work through a short checklist rather than rewriting the formula at random:

Confirm the start date is not later than the end date, and confirm the year is correct.
Check that comparison operators are inside quotation marks and joined to dates with &.
Use DATE or cells containing real dates instead of ambiguous date text typed directly into a criterion.
Look for text dates, hidden times, blanks, and values imported with inconsistent formats.
Make sure every criteria range covers the same rows and that other conditions are not excluding the records.
Check calculation mode if a formula appears stale. On Windows, use Formulas > Calculation Options > Automatic; Mac has Calculation Options on the Formulas tab as well.

Excel’s formula argument separator can depend on regional settings. The formulas here use commas, as required by the usual English-language syntax; if your Excel installation expects semicolons, replace argument-separating commas with semicolons. Do not change commas inside quoted text unless they are actually argument separators.

Another useful distinction: a cell’s display format is not the same as its stored value. Applying a Short Date format may make a serial number easier to read, but it will not turn a text string into a serial date. Conversely, a true date can display as a number if the cell is formatted as General. Check the value and the criteria logic, not just the visible appearance.

Frequently Asked Questions

Why does COUNTIFS return zero even though the dates look right?
They may be text rather than Excel date values, the criteria may be quoted incorrectly, or the formula may use the wrong year or date boundaries. Test a few cells and verify the start and end values before changing the whole column.

Does COUNTIFS include the end date?
It does if the condition is inclusive and the stored value is exactly at the boundary. For data that may include times, use a less-than condition for the day after the final date; that includes all times on the final calendar day.

How do I count dates in a month when the month length changes?
Use the first day of the month as the inclusive lower boundary and the first day of the next month as the exclusive upper boundary. That works for February, leap years, and months with 30 or 31 days without manually calculating the last day.

Can COUNTIFS criteria use dates stored in cells?
Yes. Join the comparison operator to the cell reference with an ampersand, and make sure the referenced cell contains a real date. This is useful when users need to change the reporting period without editing the formula.

Why does a date range work in one workbook but not another?
The workbooks may use different regional date conventions, contain values imported in different formats, or have different calculation settings. Compare underlying values and date boundaries, not only the way the cells are displayed.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.