How to Use COUNTIFS with Date Range in Excel

Using COUNTIFS with Date Range in Excel means you want to apply COUNTIFS to count something between 2 dates, e.g., counting the number of records falling within a defined time frame.

This technique is particularly useful for data analysis and reporting tasks where understanding the frequency of events or occurrences within specific date boundaries is crucial.

In this Excel tutorial, you will learn how to use COUNTIFS with date range in Excel.

Let’s say, I have the following sales data containing columns named Order Date, Region, City, Category, and Quantity. In the following image, I have calculated the number of orders between Nov 1, 2020, and Oct 31, 2022 date range.

In this blog post, you will learn how to use the COUNTIFS function for a date range in the following cases:

– Counting how many dates are there in a date range,
– Counting the occurrence of a year in a date range,
– Counting occurrence of a specific date in a date range &
– Counting the occurrence of dates in a date range while matching other criteria.

While analyzing sales performance, tracking employee attendance, managing inventory, monitoring project task completion, assessing customer engagement, or many more tasks, you may need to use the Excel COUNTIFS function with date range.

Note:

• I have used Microsoft 365 version for this article but you can use any later version of Excel 2010.
• Excel COUNTIFS and SUMPRODUCT functions were introduced from the Excel 2010 version.

⏷ Case 1: Count Dates in a Date Range
⏷ Case 2: Count Occurrence of a Specific Year in a Date Range
⏷ Case 3: Count Occurrence of a Specific Date in a Date Range
⏷ Case 4: Count Dates in a Date Range That Match Another Criteria

How Does COUNTIFS Work with a Date Range?

The COUNTIFS function counts the number of cells that meet a set of conditions.

The basic syntax of the COUNTIFS function is as follows:

=COUNTIFS(range1, criteria1, range2, criteria2, …)

Since, Excel considers dates as serial numbers (‘Jan 1, 1900’ is counted as 1), you can use dates with >, <, >=, <= etc. logical operators to set conditions inside the COUNTIFS formula.

For example, if you want to count the number of orders made in January 2021, you can use the formula:

Case 1: Using the COUNTIFS Function to Count Dates in a Date Range

Here, I have a sample dataset that contains Order Date, Region, City, Category, and Quantity columns.

Now, I will count the number of orders between November 1, 2021, and October 31, 2022, using the COUNTIFS function.

Follow the steps below:

Step 1: In cell H6, enter the start date of 11/1/2020 (the standard date format is mm/dd/yy).

Step 2: In cell I6, enter the end date 31/10/2022.

Last Step: Now in cell H10, insert the following formula to count the number of orders within the specified date range:

The formula then returns the total count of cells that meet both of these conditions. And gives us the number of orders made within the specified date range.

Note:

If any date occurs more than once, COUNITFS counts all the occurrences, i.e., if 11/6/2020 is recorded 2 times, COUNTIFS considers both the records in the count.

Case 2: Using COUNITFS to Count Occurrence of a Specific Year in a Date Range

Now, our goal is to count the number of orders made in a particular year (i.e. 2021). Also, for this case, we will use a date range that starts from the first day (i.e. 01/01/2021) of the year 2021 till the last day (i.e. 12/31/2021) of 2021.

Follow the given steps for this:

Step 1: First, select a cell where you will enter the Year (i.e. H6)

Step 2: Now, choose an empty cell (i.e. H9) where you want the number of orders made in the target year.

Step 3: Now, in cell H9, insert the following formula:

Syntax of DATE function is: DATE(year, month, day)

So, in our formula, DATE(H6, 1, 1) and DATE(H6, 12, 31) return the first date and the last date of the year inserted in cell H6.

Last Step: After inserting the formula, press Enter, and the result in cell H9 will be the number of orders made in the year specified in the cell.

Case 3: Using COUNTIFS to Count Occurrence of a Specific Date in a Date Range

Now you will learn how to use the Excel COUNTIFS function to count the number of orders that occurred on a specific date (i.e. 11/12/2021).

In other words, you will have to count the occurrence of a specific date in a date range.

Do to following steps:

Step 1: First of all, enter the date in the target cell (i.e. H6).

Type the date in the mm/dd/yyyy format. For example, In this case, enter “11/12/2021”.

Step 2: Now, in cell H11, insert the following formula to count the number of orders that occurred on November 12, 2021:

Last Step: Press Enter, and the result in cell H11 will show the number of orders that occurred on November 12, 2021.

Case 4: Using COUNITFS to Count Dates in a Date Range That Match other Criteria

Now you will see how to use COUNTIFS to count the number of orders within a date range while considering additional criteria, such as a specific region. Here, I will count the number of orders in the “West” region from 11/01/2021 to 12/31/2021.

Do the following steps:

• In cell H6, enter the start date of the date range (i.e. 11/1/2021), and In cell I6, enter the end date (i.e.12/31/2021)
• In cell J6, enter the region for which you want to count orders. For this example, I will use “West” as the region.

• Now, in cell J6 insert the following formula to count the number of orders in the “West” region within the date range:

• Press Enter to get the number of orders in the “West” region within the specified date range

Read More: How to Use COUNTIFS for 3 Criteria

Download Practice Workbook

In this article, you’ve learned how to use the Excel COUNTIFS function for a specific date range. I explained how COUNTIFS works with date ranges and showed its usage for counting orders within specific date ranges. I also showed how to use the COUNTIFS function to count orders within a particular year and on specific dates. Additionally, I introduced the SUMPRODUCT function as an alternative method for counting dates within a range. Was this article helpful?- Let us know in the comment box.

Related Articles

• Advanced Use of COUNTIFS Function in Excel
• Use COUNTIFS for Cells Not Equal to Multiple Text in Excel

<< Go Back to Excel COUNTIFS Function | Excel Functions | Learn Excel