How to Use COUNTIFS with Date Ranges in Excel

When working with data in Excel, you might need to count occurrences within specific date boundaries. The COUNTIFS function allows you to do just that. Whether you’re analyzing sales data, tracking employee attendance, or managing inventory, understanding the frequency of events within a date range is crucial. In this tutorial, we’ll explore how to use COUNTIFS for various date-related scenarios.

Understanding COUNTIFS and Date Ranges

The COUNTIFS function counts the number of cells that meet multiple conditions. When dealing with dates, Excel treats them as serial numbers (with ‘Jan 1, 1900’ as 1). You can use logical operators such as >, <, >=, and <= to set conditions within the COUNTIFS formula.

Note:

• 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

Case 1 – Counting Dates in a Date Range

Suppose you have a dataset with columns like Order Date, Region, City, Category, and Quantity. Let’s count the number of orders made between November 1, 2021, and October 31, 2022.Here, I have a sample dataset that contains Order Date, Region, City, Category, and Quantity columns.

• Set Up Your Data:
  • Organize your data with an Order Date column.
  • Define the date range (Nov 1, 2021, to Oct 31, 2022).

• Calculate the Count:
  • In cell H6, enter the start date (11/1/2020).
  • In cell I6, enter the end date (10/31/2022).

• • In cell H10, enter the following formula:

This formula counts the orders 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 twice, COUNTIFS considers both the records in the count.

Case – Counting Occurrence of a Specific Year

Now let’s count the number of orders made in the year 2021. We’ll use a date range from January 1, 2021, to December 31, 2021.

• Specify the Year:
  • In cell H6, enter the year (2021).

• Calculate the Count:
  • Choose an empty cell (e.g., H9).

• • Use the following formula:

Here, DATE(H6, 1, 1) and DATE(H6, 12, 31) represent the first and last dates of 2021.

The result in cell H9 will be the number of orders made in 2021.

Case 3 – Counting the Occurrence of a Specific Date in a Date Range

In this scenario, you’ll learn how to use the Excel COUNTIFS function to count the number of orders that occurred on a specific date (e.g., November 12, 2021). Essentially, you’ll determine the occurrence of that specific date within a given date range.

Follow these steps:

• Enter the Target Date:
  • In cell H6, enter the date you want to analyze (e.g., “11/12/2021”).

• Calculate the Count:
  • In cell H11, insert the following formula:

This formula counts the number of orders that occurred on November 12, 2021.

• Press Enter:
  • The result in cell H11 will show the total count of orders for that specific date.

Case 4 – Counting the Dates in a Date Range That Match other Criteria

Now let’s explore how to use COUNTIFS to count the number of orders within a date range while considering additional criteria, such as a specific region. For this example, we’ll count the orders in the “West” region from November 1, 2021, to December 31, 2021.

Follow these steps:

• Specify the Date Range:
  • In cell H6, enter the start date of the date range (e.g., 11/1/2021).
  • In cell I6, enter the end date (e.g., 12/31/2021).

• Define the Region:
  • In cell J6, enter the region for which you want to count orders (e.g., “West”).

• Calculate the Count:
  • In cell J6, insert the following formula:

This formula counts the number of orders in the “West” region within the specified date range.

• Press Enter:
  • The result will display the total count of orders in the “West” region during the specified date range.

Read More: How to Use COUNTIFS for 3 Criteria

Download Practice Workbook

You can download the practice workbook from here:

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