Conditional formula page

COUNTIFS Between Dates Formula

Q: 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.

Q: 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.

Use COUNTIFS with date boundaries when the report needs a row count instead of a total.

Best for

Count rows between two dates with COUNTIFS.

What it returns

If F1 is 2026-01-01 and G1 is 2026-02-01, the formula returns 2 for the sample data.

Copy formulas

Excel formula

=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1)

Google Sheets formula

=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1)

Excel / Google Sheets difference

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

What it returns

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

January with the next month as the boundary

=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1)

Excel: Enter 2026-01-01 in F1 and 2026-02-01 in G1. Returns: 2

Inclusive January end with possible timestamps

=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1+1)

Google Sheets: Enter 2026-01-01 in F1 and 2026-01-31 in G1. Returns: 2

Inclusive end for date-only cells

=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

January dates in the East region

=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.