April 12, 2022

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.

- 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 occur 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(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))`

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 `">="`

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

About me

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?

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.

View coursesExcel 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-2022 ExcelExplained.com

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