You can use the following syntax to count the number of cell values that fall in a date range in Excel:
=COUNTIFS(A2:A11,">="&D2, A2:A11,"&E2)
This formula counts the number of cells in the range A2:A11 where the date is between the dates in cells D2 and E2.
The following example shows how to use this syntax in practice.
Example: Use COUNTIFS with Date Range in Excel
Suppose we have the following dataset in Excel that shows the number of sales made by some company on various days:
We can define a start and end date in cells D2 and E2 respectively, then use the following formula to count how many dates fall within the start and end date:
=COUNTIFS(A2:A11,">="&D2, A2:A11,"&E2)
The following screenshot shows how to use this formula in practice:
We can see that 3 days fall between 1/10/2022 and 1/15/2022.
We can manually verify that the following three dates in column A fall in this range:
- 1/12/2022
- 1/14/2022
- 1/15/2022
If we change either the start or end date, the formula will automatically update to count the cells within the new date range.
For example, suppose we change the start date to 1/1/2022:
We can see that 8 days fall between 1/1/2022 and 1/15/2022.
Additional Resources
The following tutorials provide additional information on how to work with dates in Excel:
How to Calculate Average If Between Two Dates in Excel
How to Calculate a Cumulative Sum by Date in Excel
How to Calculate the Difference Between Two Dates in Excel