How to count cells between two dates using COUNTIFS

You can use the COUNTIFS function to count the number cells between two dates of an Excel file. Example file included.

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.

Step by step COUNTIFS formula with two dates

1. Type =COUNTIFS(

2. Select or type the range reference for criteria_range1. In my example, I used a named range: Birthday.

3. Insert criteria1. I wanted to count all birth dates after January 1st, 1985, so I inserted ">="&DATE(E3,1,1) where cell E3 contains the year 1985.

4. Select your date range again. Since we want to apply two criteria for the same data set, you will need to select the same range again.

5. Insert criteria2, which is the maximum date we are interested in. In my case, I wanted to count the birth dates which occurred during 1985, which means a maximum date of December 31st, 1985, so I used "<="&DATE(E3,12,31).

6. Type ) and then press Enter to complete the COUNTIFS formula.

COUNTIFS with dates - The Excel syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

where:
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 defines which cells are 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 defines which cells are 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))

How to use this COUNTIFS formula with multiple criteria

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. To add the date, you can either select it from a cell or create it using the DATE function as I did below.

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. The COUNTIFS function will return the number of cells that have dates between the two specified days if both COUNTIFS criteria are met.

When using COUNTIFS with dates, it's important to remember to use the same COUNTIFS date range. Please note that the range "Birthday" contains cells C3:C26 from the table. 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 logical operators ">=" and "<=" 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.

The following formula counts the cells between two dates by referencing the cells directly, without using the DATE function. I've used the same conditions: ">="&E3 and "<="&F3 and the result is the same as in the previous example.

You can also use this formula to count cells between two numbers in the same way you are using COUNTIFS with two dates.

What to do next?

In this article, I've shown you two examples of how to count cells between two dates using the COUNTIFS function. While this can be useful for your job, I encourage you to learn more.

If you still struggle and have additional questions about how to use COUNTIFS with date ranges and multiple criteria, please let me know by posting a comment.