Table of Contents
Using COUNTIFS with a date range in Excel is a great way to quickly count the number of cells within a certain date range. It can be used to count the number of entries between two dates, or to count the number of entries that fall within a certain month. To use COUNTIFS with a date range, you will need to enter the date range criteria in the same row or column as the relevant dates, and then use the COUNTIFS function to count the number of dates that meet the criteria.
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:
The following tutorials provide additional information on how to work with dates in Excel: