Why does COUNTIFS miss the last day of a date range?
Formula (Excel and Google Sheets)
=COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1)The dates have times, so "<="&end stops at midnight. Count up to the next day instead: =COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1).
- Result on the example
- 5 in
E3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.
How it works
The tickets carry a time as well as a date. "<="&E2 means up to midnight at the start of October 31, so the two tickets created later that day are left out. Counting up to, but not including, the next day ("<"&E2+1) takes in the whole last day: 5 tickets.
=COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1)Result5
A2:A87 rows × 1 col: 9/30/2026 14:24:00, 10/1/2026 9:36:00, 10/15/2026 18:00:00, 10/31/2026 4:48:00, …A range: 7 cells in one column.
E110/1/2026The value in E1.
">="&E1">=46296"The & operator joins as text.
E210/31/2026The value in E2.
E2+146327The + operator adds.
"<"&E2+1"<46327"The & operator joins as text.
COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1)5COUNTIFS: Counts rows that meet every condition.- criteria_range1
A2:A87 rows × 1 col: 9/30/2026 14:24:00, 10/1/2026 9:36:00, 10/15/2026 18:00:00, 10/31/2026 4:48:00, … - criteria1
">="&E1">=46296" - criteria_range2
A2:A87 rows × 1 col: 9/30/2026 14:24:00, 10/1/2026 9:36:00, 10/15/2026 18:00:00, 10/31/2026 4:48:00, … - criteria2
"<"&E2+1"<46327"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E2+1). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((INT(A2:A8)>=E1)*(INT(A2:A8)<=E2)) | 5 | Drops the times with INT, then compares whole dates. |
Common mistakes
- A date-only format hides the times, so the data looks like whole dates. Switch to a date and time format to check.
- The same fix applies to SUMIFS and AVERAGEIFS with date ranges.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does COUNTIFS miss the last day of a date range
E3, count the tickets created from the date in E1 through the whole of the date in E2.Questions
How do I count a single day?
=COUNTIFS(A2:A8,">="&E1,A2:A8,"<"&E1+1).
How do I strip the times from the column?
A helper column with =INT(A2), formatted as a date.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.