You can use the COUNTIFS function to count the number of cells between two dates of an Excel file. In this example, the COUNTIF function isn't suitable because you cannot use COUNTIF for multiple criteria, and we want to count the number of Excel records that fall between two dates.
">="&DATE(E3,1,1), where cell E3 contains the year 1985.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
criteria_range1 - the first range to compare against your criteria (Required)
criteria1 - The criteria to use on range1. It can be a number, expression, cell reference, or text that define which cells will be counted (Required)
criteria_range2 - the second range to compare against your criteria (Optional)
criteria2 - The criteria to use on range2. It can be a number, expression, cell reference, or text that define which cells will be counted (Optional)
In our example, cell F3 contains the following formula to count if the date is between two dates:
=COUNTIFS(Birthday, ">="&DATE(E3,1,1), Birthday, "<="&DATE(E3,12,31))
Since we need to check for two conditions, the COUNTIFS function is appropriate because this Excel function can easily count the number of entries between two cell values.
The first condition in cell F3
Birthday,">="&DATE(E3,1,1) checks if the birth date in the COUNTIFS date range is greater than or equal to January 1st, 1985, while the second one
Birthday,"<="&DATE(E3,12,31) checks if the birth date is less than or equal to December 31st, 1985. COUNTIFS function will return the number of cells that have dates between our two specified days if both COUNTIFS criteria are met.
Please note that the range "Birthday" contains cells C3:C26 from my table. When using COUNTIFS with dates, it's important to remember to use the same COUNTIFS date range. This means that you need to use the same cell reference for both criteria, or else Excel will return the #VALUE! error message.
Since the operators
"<=" need to be entered as text between double quotes, we have to use the symbol
& to concatenate the operator with each date. If you skip this step, Excel will not be able to understand your formula and will display an error message.
You can use the same formula to count cells between two numbers the same way you are using COUNTIFS with two dates. All you need to do is remove the DATE function from the COUNTIFS formula, and you are good to go.
You can learn more by reading another tutorial on how to count cells equal to a specific value.
Alternatively, if you have additional questions about how to use COUNTIFS with date ranges and multiple criteria, please let me know by posting a comment.
My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.
If you'd like to get in touch, you can contact me on LinkedIn.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?