Shakil Ahmed
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 4 suitable examples to count (COUNTIF) between two dates in Excel. If you are also curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
4 Suitable Examples to Use COUNTIF Between Two Dates in Excel
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 COUNTIF Function
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.
- Similarly, input a similar type of formula in the rest of the cell from F6:F10.
- 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. Combine COUNTIF Function with DATE Function
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.
- Next, drag the Fill Handle icon to copy the formula up to cell F10.
- 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
- Excel COUNTIFS Not Working (7 Causes with Solutions)
- COUNTIF vs COUNTIFS in Excel (4 Examples)
- VBA COUNTIF Function in Excel (6 Examples)
- Count Blank Cells with Excel COUNTIF Function: 2 Examples
- How to Apply Excel COUNTIF with Pivot Table Calculated Field
3. Counting Number of Dates by SUMPRODUCT Function
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.
- Similarly, insert a similar type of formula in the rest of the cell from F6:F10.
- 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. Count 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.
- 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
Apply COUNTIF 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.
- After that, drag the Fill Handle icon to copy the formula up to cell F10.
- 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.
Use 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.
- 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.
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, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Articles
- Compare Two Tables and Highlight Differences in Excel (4 Methods)
- How to Use COUNTIF Between Two Numbers (4 Methods)
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
- Excel COUNTIF Function to Count Cells Greater Than 0
- How to Apply COUNTIF Between Two Cell Values in Excel
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)
