COUNTIF Between Two Dates in Excel (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

For different purposes, you may need to find the number of occurrences between two dates. You can fetch the count value easily once you have known the techniques. Today, in this article, we are going to demonstrate 6 suitable examples of count (COUNTIF) between two dates in Excel. If you are also curious about it, download our practice workbook and follow us.


To demonstrate the examples, we consider a dataset of 12 people and their date of birth. The people’s name is in column B, and their date of birth is in column C. We will count the number of years which are mentioned in column E.


1. Inserting Dates Directly into COUNTIFS Function to Count Between Two Dates

In the first example, we are going to directly input the dates into the COUNTIFS function to count the number of dates. The steps to complete this example are given below:

📌 Steps:

  • First of all, select cell F5.
  • Now, write down the following formula in the cell.

=COUNTIFS($C$5:$C$16,">=01-01-1990",$C$5:$C$16,"<=12-13-1990")

  • Press Enter.

Inserting Dates Directly into COUNTIF Function

  • Similarly, input a similar type of formula in the rest of the cell from F6:F10.

For Counting Between Two Dates Insert Dates Directly into the COUNTIF Function

  • You will get all the counted values at our desired cell.

Thus, we can say that our formula works perfectly, and we are able to use the COUNTIFS function to count between two dates.

Read More: COUNTIF That Does Not Contain Multiple Criteria in Excel


2. Combining Excel COUNTIFS & DATE Functions to Count between Two Dates

In the second example, we will use the DATE and  COUNTIFS functions to count the number of dates. The steps to finish this example are given as follows:

📌 Steps:

  • First, select cell F5.
  • After that, write down the following formula in the cell.

=COUNTIFS($C$5:$C$16,">="&DATE(E5,1,1),$C$5:$C$16,"<="&DATE(E5,12,31))

  • Then, press Enter.

Combination of COUNTIFS and DATE Function

  • Next, drag the Fill Handle icon to copy the formula up to cell F10.

Apply COUNTIF Function with DATE Function to Count Between Two Dates

  • You will notice that the formula will estimate the number of years at our desired cells.

Hence, we can say that our formula works effectively, and we are able to use the COUNTIFS function to count between two dates.

🔎 Breakdown of the Formula

We are breaking down the formula for cell F5.

👉 DATE(E5,1,1): The DATE function will convert the numerical value into the date value. Here, the value is 1/1/1990.

👉 DATE(E5,12,31): The DATE function will convert the numerical value into the date value. For this function, the value is 12/31/1990.

👉 COUNTIFS($C$5:$C$16,”>=”&DATE(E5,1,1),$C$5:$C$16,”<=”&DATE(E5,12,31)): The COUNTIFS function will count those value of dates whose the lie between the date 1/1/1990 and 12/31/1990. Here, the value is 1.

Read More: COUNTIF Date Is within 7 Days


Similar Readings


3. Counting Number of Dates by SUMPRODUCT Function between Two Dates

In the following example, the SUMPRODUCT and DATEVALUE functions will help us to count the number of dates. The steps to accomplish this example are shown as follows:

📌 Steps:

  • At first, select cell F5.
  • Afterward, write down the following formula in the cell.

=SUMPRODUCT(($C$5:$C$16>=DATEVALUE("1/1/1990"))*($C$5:$C$16<=DATEVALUE("12/31/1990")))

  • Now, press Enter.

Applying SUMPRODUCT and DATEVALUE Function

  • Similarly, insert a similar type of formula in the rest of the cell from F6:F10.

Applying SUMPRODUCT and DATEVALUE Function to Count Between Two Dates

  • You will see that the formula will calculate the number of years, like in the previous example.

Therefore, we can say that our formula works precisely, and we are able to count between two dates.

🔎 Breakdown of the Formula

We are breaking down the formula for cell F5.

👉 DATEVALUE(“1/1/1990”): The DATEVALUE function will convert the numerical value into the date value. Here, the value is 1/1/1990.

👉 DATEVALUE(“12/31/1990”): The DATEVALUE function will convert the numerical value into the date value. For this function, the value is 12/31/1990.

👉 SUMPRODUCT(($C$5:$C$16>=DATEVALUE(“1/1/1990”))*($C$5:$C$16<=DATEVALUE(“12/31/1990”))): The SUMPRODUCTS function will count the value of dates which are lied between the date 1/1/1990 and 12/31/1990. Here, the value is 1.

Read More: How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)


4. Counting Between Any Given Dates Range

In the last example, we are going to find out the number of dates in a date range using the COUNTIFS function. Our desired date range is in the range of cells E5:F5.

The procedure is explained below step-by-step:

📌 Steps:

  • Firstly, select cell G5.
  • Next, write down the following formula in the cell.

=COUNTIFS($C$5:$C$16,">="&E5,$C$5:$C$16,"<="&F5)

  • After that, press Enter.

Count Between Any Given Dates Range

  • You will get the total number of entities in cell G5.

Finally, we can say that our formula works successfully, and we are able to use the COUNTIF function to count between two dates.

Read More: How to Use COUNTIF to Count Date Less Than Today in Excel


5. Applying COUNTIFS Function Between Two Dates with Multiple Criteria

Besides the previous examples, we will show you the procedure to use the DATE and  COUNTIFS functions to count the number of dates for multiple criteria. The steps of this procedure are given as follows:

📌 Steps:

  • In the beginning, select cell F5.
  • Now, write down the following formula in the cell.

=COUNTIFS($C$5:$C$16,">="&DATE(E5,1,1),$C$5:$C$16,"<="&DATE(E5,12,31))

  • Press Enter.

Utilizing DATE and COUNTIFS Function

  • After that, drag the Fill Handle icon to copy the formula up to cell F10.

COUNTIF Between Two Dates with Multiple Criteria

  • You will get all the number of estimated years at our desired cells.

At last, we can say that our formula works fruitfully, and we are able to use the COUNTIFS function to count between two dates for multiple criteria.

🔎 Breakdown of the Formula

We are breaking down the formula for cell F5.

👉 DATE(E5,1,1): The DATE function will convert the numerical value into the date value. Here, the value is 1/1/1990.

👉 DATE(E5,12,31): The DATE function will convert the numerical value into the date value. For this function, the value is 12/31/1990.

👉 COUNTIFS($C$5:$C$16,”>=”&DATE(E5,1,1),$C$5:$C$16,”<=”&DATE(E5,12,31)): The COUNTIFS function will count those value of dates whose the lie between the date 1/1/1990 and 12/31/1990. Here, the value is 1.


6. Using Excel COUNTIF Function Between Two Dates with Matching Criteria

In this case, we will find out the exact number of dates in our dataset using the COUNTIF function. The date which we are going to search is cell E5.

The procedure is described below step-by-step:

📌 Steps:

  • In the starting, select cell F5.
  • Afterward, write down the following formula in the cell.

=COUNTIF($C$5:$C$16,E5)

  • Press the Enter key.

COUNTIF Between Two Dates with Matching Criteria

  • You will notice the total number of entities in cell F5.

In the end, we can say that our formula works properly, and we are able to use the COUNTIF function to count between two dates for matching criteria.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to use the COUNTIF function to count between two dates in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

2 Comments
  1. I love how you use Excel to do calculations!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo