How to count cells between two dates in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2)Use COUNTIFS with two conditions on the same date column: on or after the start date, and on or before the end date.
- Result on the example
- 6 in
G3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIFS counts a row only when every condition is true, so two conditions on the same column make a range: ">="&G1 keeps dates on or after the start, and "<="&G2 keeps dates on or before the end. The & joins the operator to the date in the cell, which is what lets you change the dates without touching the formula.
Dates are numbers underneath, so the comparison works on the real date, not the way it is displayed.
=COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2)Result6
B2:B1110 rows × 1 col: 2/14/2026, 2/27/2026, 3/2/2026, 3/9/2026, …A range: 10 cells in one column.
G13/1/2026The value in G1.
">="&G1">=46082"The & operator joins as text.
G23/31/2026The value in G2.
"<="&G2"<=46112"The & operator joins as text.
COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2)6COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1110 rows × 1 col: 2/14/2026, 2/27/2026, 3/2/2026, 3/9/2026, … - criteria1
">="&G1">=46082" - criteria_range2
B2:B1110 rows × 1 col: 2/14/2026, 2/27/2026, 3/2/2026, 3/9/2026, … - criteria2
"<="&G2"<=46112"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2). We ran it in Google Sheets mode on the same data and it returns 6.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B11>=G1)*(B2:B11<=G2)) | 6 | Works in every version, and lets you add conditions COUNTIFS can't express, like a test on MONTH(B2:B11). |
| =COUNTIFS(B2:B11,">"&G1,B2:B11,"<"&G2) | 5Different result | Use > and < when the start and end dates themselves should not count. Leaves out orders dated exactly on G1 or G2. |
Common mistakes
- Typing the date inside the quotes, like
">=3/1/2026", works only in date formats that match your computer's settings. Keep dates in cells and join them with&. - Dates with times (a timestamp like 3/31/2026 4:30 PM) are larger than the plain date, so
"<="&G2misses them. Use"<"&G2+1to include the whole last day. - Dates stored as text don't count. If the dates sit on the left of the cell, convert them first.
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.
Count cells between two dates
G3, count the orders dated from the start date in G1 to the end date in G2, both days included.Questions
How do I count between two dates and another condition?
Add another pair to COUNTIFS, for example =COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2,C2:C11,"East").
How do I count dates in one month?
Put the first day of the month in G1 and use EOMONTH(G1,0) for the end: =COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&EOMONTH(G1,0)).
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.