Count cells between two dates using COUNTIFS

You can use the COUNTIFS function to count the number of cells between two dates of an Excel file. In this example, COUNTIF function isn’t suitable because you cannot use COUNTIF for multiple criteria (it’s limited to just one).

COUNTIFS between two dates

The 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 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))

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 occur 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.

How to use this COUNTIFS formula with multiple criteria

Since we need to check for two conditions, the use of 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.

Since the 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.

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 to remove the DATE function from the COUNTIFS formula and you are good to go.

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

5 comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Hey – thanks for the helpful article. I’m looking to do something specific using this formula. I’m trying to figure out a way to count the number of cells that contain specific string values, that are also between two dates. Here’s an example screenshot: https://puu.sh/GT8de/0973ba05b2.png

    Basically, I’m trying to create a formula that will allow me to count how many times people were booked with certain clinicians, each month (so, how many cells in column B contain a specific string such as ‘Booked with Kim’, during July as indicated in column A?). Is there any way to do this using this formula? Thank you!

  • =countif(I2:I675,”>=”&DATE(2021,1,1),I2:I675,”<="&DATE(2021,31,1))

    Hi there, I am using your formula to look at how many articles in a database will expire each month (I2:I675). Everytime I put the above formula in it comes back with "you've entered too many arguments for this function." What is going wrong?

    • Hi Aaron,

      There are two problems with your formula:

      1. The quotation symbols that you’ve used are different. The first three are (”), while the last one is (“). Make sure your quotation symbols are consistent because Excel gets confused otherwise.
      2. You have provided two conditions for the COUNTIF function, which only uses one. Rewrite your formula using COUNTIFS.

      All the best,
      Radu