How to 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, 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
- Type =COUNTIFS(
- Select or type the range reference for criteria_range1. In my example, I used a named range: Birthday.
- 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.
- 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.
- 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). - 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 at your job, I encourage you to learn more by reading some of the following articles:
- How to count cells equal to a specific value
- How to sum sales by year
- How to separate first and last name
- How to count cells that contain odd numbers
- How to subtotal by item type
- How to use an IF function with multiple conditions
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.
Hello Radu, I am having an issue using a similar formula. It almost works but not quite! It is over-counting in some instances.
The formula I am using is: =COUNTIFS(‘STH SLV’!$B:$B, C4, ‘STH SLV’!$H:$H, “>=”&$H$2, ‘STH SLV’!$H:$H, “=”&$H$2, ‘STH SLV’!$I:$I, “=”&$H$2, ‘STH SLV’!$J:$J, “<="&$H$3) B:B Matches a staff number to C4. I then want it to look at 5 columns that contain date. If the staff number and the dates match the given criteria, it should count it. In the above, the result should be 2 but it is returning 3. Can you help 🙂
Hi Steph,
What I see in your formula is that you are overlapping some of the criteria. Why do you compare STH SLV’!$H:$H to $H$2 twice? You have STH SLV’!$H:$H,”>=”&$H$2 and also STH SLV’!$H:$H,”=”&$H$2
Hello Radu, my issue I want to count the values within 1 month that are also above a certain value (say 20%). The way my formula is set up now is it counts the values above 20% but not for 30 days or 1 month but from the date I have entered all the way until present or all the way back in time. I would like to just count the values within a given month and not all beyond or before a period of time. =COUNTIFS(B52:B999999, “>12/31/19”, M52:M999999,”<20%") Any help would be greatly appreciated thank you for your educational post I know many people included myself appreciate it.
Hi Duck,
I’m sorry for the late reply. I was out of town for a few days. I think the formulas you are looking for is:
=COUNTIFS(B52:B999999, “>=”&DATE(2019,12,1), B52:B999999, “<="&DATE(2019,12,31), M52:M999999, ">“&20%)
Let me know if it works as intended.
Hi Radu,
Im also very new to excel but am trying to do something a little more complex with the same idea. Im trying to display a count of how many “jobs” or “Cells” per say, fit multiple creiteria located on another sheet. for instance i want to display on sheet 1, how many “jobs” or “cells” on sheet 2 are within the month of october (Data is in cells H:H) and are also has a specific persons Name in another cell on the same row (Names are in cells G:G).
Is this possible? and how would i look at building this equasion?
Thanks, Hayden.
Hi Hayden,
That is definitely possible. A simple COUNTIFS formula with 3 conditions should do the trick. Just use the two conditions shown in the article and add a third set of conditions like this (G:G, name_of_person). Select the cell where the name_of_person is stored and you should have the formula you are after.
Let me know if you run into any issues.
All the best,
Radu
Hi Radu,
The equasion currently looks like this
=COUNTIFS(‘OVERDUE TASKS’!H6:H10000, “>=”&DATE(2021, 10, 1), ‘OVERDUE TASKS’!H6:H10000, “=”&DATE(2021, 10, 1), ‘OVERDUE TASKS’!H6:H10000, “<="&DATE(2021, 10, 31))
&
=COUNTIFS('OVERDUE TASKS'!G6:G1000, "ISS Roy Hill Plumbing WG")
work well, but together they keep spitting out #VALUE!, do you have any ideas where im going wrong?
If I got your sheet name correctly I think the correct formula for you would be:
=COUNTIFS(‘OVERDUE TASKS’!H6:H10000, “>=”&DATE(2021, 10, 1), ‘OVERDUE TASKS’!H6:H10000, “<="&DATE(2021, 10, 31), 'OVERDUE TASKS'!G6:G10000, "ISS Roy Hill Plumbing WG") Let me know if it works as intended.
How do you count time between two dates and not count weekend or holidays. I want time, not days necessarily.
Hi Lorenzo,
I think the easiest way would be to use NETWORKDAYS.INTL function to count how many whole working days fall between your dates. Then, you can make any adjustments needed to correctly reflect the time.
Cheers,
Radu
Hello!
I am not great at excel and I have a dilemma. I work in a hotel that has a lot of different bed types. We often have to put in reservations ourselves for conference attendees. We have a system but it is not efficient. I’d like to find a way to “count if” the person is assigned a certain room type for the nights of their stay. For example, 03/31/22 – 04/03/22 would be three nights when ordinary count formulas would count 04/03).
We have a template set up that counts the number of arrivals, departures, and rooms sold each night, but I can’t figure out how to make it work with assigning each attendee a room type.
Any help is greatly appreciated!
Hi Lauren,
It’s a bit hard to understand what you try to achieve without seeing the file. If you’d like, you can send me a message via the contact form and I’ll look at your file privately.
All the best,
Radu
Hi, just a quick one here, how do we use COUNTIFS from the DATEDIF?
I’ve used the DATEDIF to calculate the time differences of a list of datas but now I do need to group and count them in few categories such as 0-2days, 1-3days, 4-6days and so on.
Please advise if this can be done and your prompt response is highly appreciated! Thank you!
Hi Sean,
Yes, this can be done. You should just use the DATEDIF result in the COUNTIF formula.
Hello! I have loved your explanations and they have really helped me. Currently, I am working on a project that really has me stumped.
Here is the project:
I am currently trying to automate a process that counts the number of tickets for events sold per week. Each week I pull data from our ticketing program that provides the date it was purchased and the name of the show. This information is in a sheet called ‘regiondo-sales-export’ so I can add the new data each week and have the counting of the tickets sold done automatically by excel.
I have created a second sheet called ‘weekly-sales-report’ where I have one column with the name of the show and one column where the counted data should appear for the counted sales each week. Above each weekly column, I have two rows, one for the date beginning the week, one for the date ending the week.
I know it is incorrect, but this is how I wrote my formula:
B:B is the date range from the extracted sales report data
J3 is beginning date
J4 is the ending date
F:F is the show title in sales report export
B7 is the title of the show in the weekly report I’m trying to match it to
=COUNTIFS(‘regiondo-sales-export’!B:B, “>=”&’weekly-sales-report’!J3, ‘regiondo-sales-export’!B:B, “=”&DATE(2022,1,10), ‘regiondo-sales-export’!B:B, “<="&DATE(2022,1,16), 'regiondo-sales-export'!F:F, 'weekly-sales-report'!B7)
Feel free to email me if you have the time to help! I'm currently studying in school and this stuff has become super interesting to me! Thanks!
Angela
Hi Angela,
I wrote you an email.
Thanks for the tutorial! I’m still having a problem with creating the correct formula. I’m trying to count the number of occurrences for a program based on a time lapse, based on TODAY(), in increments of 1 month, 3 months. 6 months, 9 months, 12 months, 15 months, 18 months, and 24 months.
Cells AB87 through AB94 have the time increments (1, 3, 6, 9, etc.)
Cells AC87 through AC94 are to count the number of occurrences in the corresponding cells in column AB, based on TODAY() . . . each day the number can change based on when the date
Cell AB85 is the program
The source data are cells AR (program) and H (issue date) on a separate worksheet (‘RAW DATA!’)
If you cannot incorporate ‘TODAY()’ into the formula, today’s date is in cell H2
I’ve been trying for three weeks to figure this out but I’m just not getting it. Any help you can provide is more than welcome. Thanks!
Hi William,
It’s hard to write a formula without seeing the actual file and what you have included there. If you’d like, I can look at the file and provide suggestions. If there aren’t any sensitive data, of course.
Just send me a message via the Contact page, and I’ll reply via email.
All the best,
Radu
Amazing tutorial! I’m working on a sheet where;
Column A is a Status with Data “Passed” and “In progress”
Column B is the date the students passed
Column C cell 1 is the Start Date
Column D cell 1 is the End Date
Now, in Column E, I want to count how many passed within the date range of Column C and D.
Please advise!
Hi Clyde,
Try this:
=COUNTIFS(A1:A100, “Passed”, B1:B100, “>=”&C1, B1:B13, “<="&D1) Just replace A1:A100 and B1:B100 with your range of cells. Alternatively, you could select the entire column, but I don't recommend it as it slows down your file. Let me know if that works. Radu
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")
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.
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
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
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!
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.
=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?
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
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!
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.
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