Conditional formula page
COUNTIFS Between Dates Formula
Use COUNTIFS with date boundaries when the report needs a row count instead of a total.
Count rows between two dates with COUNTIFS.
If F1 is 2026-01-01 and G1 is 2026-02-01, the formula returns 2 for the sample data.
Copy formulas
=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1)=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1)Excel and Google Sheets use the same COUNTIFS date syntax. The upper boundary should be chosen according to whether the source contains date-only or date-time values.
Example data
| Date | Region | Product | Amount | Rep |
|---|---|---|---|---|
| 2026-01-04 | East | Widget | 420 | Maya |
| 2026-01-12 | West | Widget | 310 | Noah |
| 2026-02-03 | East | Gadget | 275 | Maya |
| 2026-02-15 | East | Widget | 640 | Iris |
If F1 is 2026-01-01 and G1 is 2026-02-01, the formula returns 2 for the sample data.
How the formula works
- The first criterion keeps rows on or after F1.
- The second criterion keeps rows before G1, so G1 should hold the next day or the first day after the reporting period.
- Use less than G1+1 when G1 is the inclusive end date and source cells may contain timestamps.
- COUNTIFS returns a row count, not an amount total.
| Syntax piece | Role in the formula |
|---|---|
| A2:A100 | The Date range tested by both the start and end criteria. |
| >=&F1 | Includes every real date value on or after the start date in F1. |
| <&G1 | Excludes G1 itself, so G1 should hold the first date outside the reporting window. |
Verified examples
=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1)Excel: Enter 2026-01-01 in F1 and 2026-02-01 in G1. Returns: 2
=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1+1)Google Sheets: Enter 2026-01-01 in F1 and 2026-01-31 in G1. Returns: 2
=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<="&G1)Excel: Enter 2026-01-01 in F1 and 2026-01-31 in G1, and confirm the source contains dates without times. Returns: 2
=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1, B2:B100, "East")Google Sheets: Use next-period date boundaries and add East as a third criterion. Returns: 1
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The count misses records later on the final day | The Date column contains times but the formula compares it to an end date at midnight. | Use <G1+1 when G1 is the inclusive final date, or place the next day in G1 and use <G1. |
| COUNTIFS returns zero for a visible date range | The source or criteria cells contain date-looking text rather than date serial values. | Convert the cells to real dates and verify one comparison before applying both boundaries. |
| An added status or region condition produces #VALUE! | The new criteria range does not have the same row boundaries as A2:A100. | Align every criteria range to rows 2 through 100. |
When not to use this formula
- Use SUMIFS when the rows inside the date window should contribute amounts instead of a simple count.
Alternatives
| Alternative | When to use it |
|---|---|
| COUNTIFS Formula Builder | Use to assemble date and non-date criteria pairs interactively. |
| SUMIFS Between Dates Formula | Use when matching rows should contribute values from an Amount column. |
Related formulas
Official references
- COUNTIFS function from Microsoft
FAQ
Does COUNTIFS include both boundary dates?
The shown >=F1 and <G1 version includes F1 but excludes G1. Put the first date outside the period in G1 for a complete closed reporting window.
How do I include an end-date cell that may have timestamps?
Use <G1+1 so every time on the calendar date in G1 remains below the following midnight.