How to Use COUNTIF Between Two Dates and Matching Criteria in Excel

The article will show you how to use Excel COUNTIF or COUNTIFS function between two dates and matching criteria. Basically, the COUNTF function counts data based on a single criterion. But when multiple ranges and matching criteria are involved, it does not have any efficient usage. But its alternative, the COUNTIFS function works perfectly in this matter. In this article, we will count a specific item that occurs multiple times within a date range.

How to Use COUNTIF Between Two Dates and Matching Criteria in Excel: 3 Efficient Ways

In the dataset, we have the Arrival Dates of some electronic devices. We will specify the product type and date range, hence we will count how many of that specific item arrived in that date range.

1. Applying Excel COUNTIFS Function Between Two Dates and Matching Criteria

In this section, we will apply the COUNTIFS function to count how many Smartphones arrived in the store between 11th and 20th October. Here’s a brief description I’ve provided.

Steps:

• First, choose some cells to define the date range and set the criteria. The Start and End Dates will be 11th and 20th October Our criteria here is Smartphone which we will match with the data in the Products column.
• Next, select another cell to count the items and type the following formula in it. Also, press the ENTER

Here, our date range is between the 11th and 20th October. So the first criterion is that the date in the date range has to be greater than or equal to the 11th October. Similarly, the second criterion will be that the date should be less than or equal to the 20th October. As the first and second criteria are dates, we select the date range (Arrival Dates) column (B5:B14). We set the matching criteria in cell F6 and we find this in the Products column. So the next Criteria Range is C5:C14 and the Criteria is F6. As the Smartphones arrived three times in that period, the formula will count 3.

Thus you can count the matching criteria using the COUNTIFS function between two dates.

Read More: Excel COUNTIF Function with Multiple Criteria & Date Range

2. Applying COUNTIFS Function Without Cell Reference

You can also hard code the two dates (Starting and Ending) and the criteria (Smartphone) in the formula to get the same result as we get in Method 1. Let’s have a look at the following description.

Steps:

• First, select a cell to count the items and type the following formula in it.
• After that, press the ENTER The operation will return the number of Smartphones arrived between 11th and 20th October.

Here we inserted the date period and criteria manually. Although this process requires less complexity, if we want to change the date range and criteria, we have to do it in the code which is time costly and not an efficient procedure. So using cell references would be the better choice.

3. Combining EOMONTH Function with COUNTIF Between Two Dates and Matching Criteria

In this method, we will select the End Date by using the EOMONTH function. It is also a good process. Basically, employees want to know the amount of shipment at the end of the month as a summary. And using this function will make the procedure easier.

Steps:

• First, choose some cells to define the date range and set the criteria. The Start and End Dates will be 11th and 31st October We set the last date of the month by the following formula.

The EOMONTH function returns the last date of the corresponding month if the second argument in the formula is 0. If the second argument is 1, then it will return the last date of the next month. Similarly, for 2, 3, 4, and so on, it will add one month prior to the other.

• In addition, our criteria here is Desktop which we will match with the data in the Products
• Next, select another cell to count the items and type the following formula in it. Also, press the ENTER

Our time frame, in this case, is from October 11 to October 20. Therefore, the first requirement is that the date within the range must be greater than or equal to October 11th. The second requirement will be the same: the date must be less than or equal to October 20. We choose the date range (Arrival Dates) column because the first and second criteria are dates (B5:B14). The matching criteria are entered in cell F6, and the Products column contains the results. Therefore, the next set of criteria has a C5:C14 range and an F6 standard. The formula will count 3 because the Desktop arrived 3 times throughout that time.

As a result, you may combine the EOMONTH function with the COUNTIFS function to count the matching criteria between two dates.

Similar Readings

• How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
• COUNTIF with Multiple Criteria in Different Columns in Excel

Useful Alternative: Using DCOUNTA and SUMPRODUCT Functions Between Two Dates and Matching Criteria

1. Application of DCOUNTA Function

Using the DCOUNTA function can also provide a similar result as we obtained in the first and second methods. But we need some additional requirements to use this function. Please follow the instructions below.

Steps:

• First, insert the cell value of B4 in E5 using the formula below.

• Next, write down the formula below in E5 and hit ENTER.

The formula uses the TEXT function to set up the Start Date criteria for the DCOUNTA function.

• Similarly, use the formula below to set up the End Date

• Thereafter, insert the cell value of F9 in G5 using the formula. This is the match criteria for the DCOUNTA function.

• Next, type the following formula in F10.

The DCOUNTA function works on a database, here the database is the entire information about the product and their arrival dates (B4:C14) which is the first argument of this function. The second argument is the field argument which is the product type referred to by G4. The final argument here is the criteria of the formula which is the range E4:G5. The formula will return 3 as the Smartphones have arrived 3 times in this period.

Thus you can apply matching criteria by the DCOUNTA function between two dates.

Read More: How to Apply SUM and COUNTIF for Multiple Criteria in Excel

2. Using SUMPRODUCT Function

Interestingly, we can apply the SUMPRODUCT function to count the number of times that Smartphones arrived between 11th and 20th October. Please follow the instructions below.

Steps:

• First, choose some cells to define the date range and set the criteria. The Start and End Dates will be 11th and 20th October Our criteria here is Smartphone which we will match with the data in the Products column.
• Next, select another cell to count the items and type the following formula in it. Also, press the ENTER

Thus you can apply the SUMPRODUCT function for matching criteria.

Read More: SUMPRODUCT and COUNTIF Functions with Multiple Criteria

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own and gain expertise.

Download Practice Workbook

Conclusion

In the end, we can conclude that the article will help you to understand how to use the COUNTIF function between two dates and matching criteria. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.