Count cells between two dates using COUNTIFS

November 18, 2018

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.

What to do next?

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

11 comments on “Count cells between two dates using COUNTIFS”

  1. Thanks for your tutorial. Can you help me understand why the first formula fails, but the second does not? A2 & A3 refer to fields that are formulated as date fields and are populated with 2021-09-13 & 2021-09-14 respectively.
    I need to find away for the formula to pull from the date field without having to put the dates in each time.

    =COUNTIFS('Upcoming Orders'!D2:D25, ">="&DATE(A2,1,1), 'Upcoming Orders'!D2:D25, "=2021-09-13", 'Upcoming Orders'!D:D, "<=2021-09-14")

    1. Hi Anthony,

      Looking at your formula I see several problems:

      1. Whenever you use multiple conditions in a COUNTIFS formula, you need to apply the criteria to the same range of cells. Your formula uses twice the range 'Upcoming Orders'!D2:D25 and once 'Upcoming Orders'!D:D (this is actually the entire column D). This will return the #VALUE! error. I strongly recommend reading this guide I wrote on how to fix Excel errors for more details.

      2. You have more conditions than needed. I understand that you want to check how many cells contain dates that fall between the two dates supplied in cells A2 and A3. For this, you only need two conditions, but you are supplying three. The formula you should use is this:

      =COUNTIFS('Upcoming Orders'!D2:D25, ">="&A2, 'Upcoming Orders'!D2:D25, "<="&A3) 3. You are using the DATE function in the wrong way. The DATE function returns the number that corresponds to the date supplied in the arguments. For example, the formula =DATE(2021,9,13) returns 44452. What your formula is doing is checking if the data from column D is greater than January 1st, 44452. If cells A2 and A3 contain dates (not text that is formatted like a date), then you don't need to use any additional functions to convert your data, as Excel is fully capable of working with dates. So you can simply use ">="&A2 as your criteria.

      Let me know if this fixed your problem.

  2. Hey. Thanks for the formula.

    I am looking to do a countif based on below:
    1. Unique ID
    2. Date
    3. Start Time
    4. End Time

    I aim to count the number of viewers for a webinar sessions during it's first live airing date and time. Currently, the data has all the viewers from the first airing until data.

    Please can you let me know if this is something that a countif can do.

    Thanks,
    Estela

    1. Hi Estela,

      I am not sure I completely understand how your data is structured. I can tell you, though, that the most challenging part is making sure you only count each Unique ID once (if that is what you are after). For that, I would recommend that you do some data cleansing first or use a helper column.

      I would say that using COUNTIFS alone won't help you achieve the correct result without some additional steps.

      If you are using Office 365, Excel has some really helpful functions like UNIQUE and FILTER, which are suitable for your task.

      I hope that helps.

      Radu

  3. Hi Radu, your article is wonderful! I've been playing with this formula for a few days now and can't seem to figure it out. I am trying to count the number of dates that fall within two dates that could change throughout the year. The first date is the Coverage Effective Date, the second Date is the Benefit Period End Date. I am trying to calculate the total Pay periods from a range of dates that fall within those two dates.
    =COUNTIFS('Benefit Pay-periods'!A1:A24, ">="&('Benefit Worksheet'!C12:E12), 'Benefit Pay-periods'!A1:A24, "<="&('Benefit Worksheet'!J9))

    Benefit Pay-Periods reflects the pay period range. 'Benefit Worksheet'!C12:E12 is where the first date is located. 'Benefit Worksheet'!J9 is where the second date is located.

    I'm wondering if the formula does not work with an actual date for the begin and end dates that could change- and if not do you have suggestions for a different formula? Any help would be appreciated!

    1. Hi Cara,

      Without actually seeing the file, it's a bit hard for me to troubleshoot your formula. However, what I did see is that your first date is actually a range 'Benefit Worksheet'!C12:E12 and not a single cell. COUNTIFS doesn't work with arrays by default, so you would need to supply only one cell as the criterion. Is there more than one date provided in range 'Benefit Worksheet'!C12:E12?

      If 'Benefit Worksheet'!C12:E12 is a merged cell, you can try to unmerge it and only reference the cell that actually contains your date. Merged cells can sometimes mess up Excel 🙂

      There is no reason why COUNTIFS can't handle a dynamic date that is updated (either automatically or manually).

      Alternatively, you could look at the SUMPRODUCT example from this article: https://excelexplained.com/count-cells-not-between-two-numbers

      Just change the signs to match your criteria, and you should be good to go.

      Let me know if any of these helps.

  4. =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). Every time I put the above formula in it comes back with "you've entered too many arguments for this function." What is going wrong?

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

  5. 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!

    1. Hi Sam. This can be achieved using this formula if the dates shown in your screenshot are from the same year. Otherwise, you would need to extract the month and the year separately and compare those values to your criteria. I will create an example file for you with a formula that I think will work for you.

    2. Hey, just wanted to let you know that I've uploaded the file containing your example. Please let me know if this is what you were looking for.

      All the best,
      Radu

Essential Functions

Excel Topics

Excel courses

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.
View courses
Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
Copyright © 2017-2021 ExcelExplained.com