Example 1 – Inserting Dates Directly into COUNTIFS Function to Count Between Two Dates
Steps:
- Select cell F5.
- Enter the following COUNTIFS function formula in the cell.
=COUNTIFS($C$5:$C$16,">=01-01-1990",$C$5:$C$16,"<=12-13-1990")
- Press Enter.
- Use a similar type of formula in the remaining cells from F6:F10.
Read More: COUNTIF That Does Not Contain Multiple Criteria in Excel
Example 2 – Combining Excel COUNTIFS & DATE Functions to Count between Two Dates
Steps:
- Enter the following formula in cell F5.
=COUNTIFS($C$5:$C$16,">="&DATE(E5,1,1),$C$5:$C$16,"<="&DATE(E5,12,31))
- Press Enter.
- Use the Fill Handle tool for the remaining cells.
Breakdown of the Formula
DATE(E5,1,1): The DATE function will convert the numerical value into the date value. The value is 1/1/1990.
DATE(E5,12,31): The DATE function will convert the numerical value into the date value. 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 which are between the dates 1/1/1990 and 12/31/1990. 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
Example 3 – Counting Number of Dates by SUMPRODUCT Function between Two Dates
Steps:
- Enter the following formula in cell F5.
=SUMPRODUCT(($C$5:$C$16>=DATEVALUE("1/1/1990"))*($C$5:$C$16<=DATEVALUE("12/31/1990")))
- Press Enter.
- Use a similar type of formula in the remaining cells from F6:F10.
Breakdown of the Formula
DATEVALUE(“1/1/1990”): The DATEVALUE function will convert the numerical value into the date value. The value is 1/1/1990.
DATEVALUE(“12/31/1990”): The DATEVALUE function will convert the numerical value into the date value. 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. The value is 1.
Read More: How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)
Example 4 – Counting Between Any Given Dates Range
Use the COUNTIFS function to find out the number of dates in a date range. Our desired date range is in the range of cells E5:F5.
Steps:
- Enter the following formula in the cell G5.
=COUNTIFS($C$5:$C$16,">="&E5,$C$5:$C$16,"<="&F5)
- Press Enter.
Example 5 – Applying COUNTIFS Function Between Two Dates with Multiple Criteria
Steps:
- Enter the following formula in cell F5.
=COUNTIFS($C$5:$C$16,">="&DATE(E5,1,1),$C$5:$C$16,"<="&DATE(E5,12,31))
- Press Enter.
- Drag the Fill Handle icon to copy the formula up to cell F10.
Breakdown of the Formula
DATE(E5,1,1): The DATE function will convert the numerical value into the date value. The value is 1/1/1990.
DATE(E5,12,31): The DATE function will convert the numerical value into the date value. 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 which are between the dates 1/1/1990 and 12/31/1990. The value is 1.
Example 6 – Using Excel COUNTIF Function Between Two Dates with Matching Criteria
Steps:
- Enter the following formula in F5.
=COUNTIF($C$5:$C$16,E5)
- Press Enter to get the result.
Download Practice Workbook
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)
I love how you use Excel to do calculations!
Dear ytMp3,
Thank you so much.
Regards
ExcelDemy
Great post! The examples you provided for using COUNTIF between two dates were really helpful. I especially appreciated the detailed explanations and the screenshots. They made it easy to follow along with my own data. Thanks for sharing!
Hello,
You are most welcome. Thanks for your feedback and appreciation. Glad to hear articles examples and detailed explanations and screenshots are helpful to you. Keep exploring Excel with ExcelDemy!
Regards
ExcelDemy