To count how many dates in a list fall between two dates, use COUNTIFS with two conditions on the same range: =COUNTIFS(A2:A17,">="&E2,A2:A17,"<="&F2), where E2 holds the start date and F2 the end date. The operators go inside double quotes and join to the cell reference with & — typing the date directly inside the quotes won’t work.
Both ends are included; drop the equal signs for an exclusive range. To type the dates into the formula rather than reference cells, build them with DATE: =COUNTIFS(A2:A17,">="&DATE(2026,1,1),A2:A17,"<="&DATE(2026,3,31)). =SUMPRODUCT((A2:A17>=E2)*(A2:A17<=F2)) gives the same answer and works in any version.
If you want the number of days between two dates rather than a count of rows, that’s simple subtraction — =B1-A1 — or =NETWORKDAYS(A1,B1) for working days only.
To count days between two dates (a range of dates), you need to use the COUNTIFS function instead of COUNTIF. To create a date range, you need to specify a lower date and an upper date. This tells Excel to count only the dates that fall within that range.
Formula to Count Days Between Two Dates
You can use the following steps:
- First, enter the COUNTIFS function in a cell.
- After that, in the criteria_range1 argument, refer to the range where you have dates.
- Next, in the criteria1 argument, enter the greater than (>) and equal sign (=) and enclose them in double quotation marks. And then, refer to the cell where you have the lower date.
- Now, in the criteria_range2, refer to the date range again.
- After that, in criteria2, enter the less than (<) and equal sign (=) and enclose them in double quotation marks. And then, refer to the cell where you have the upper date.
- In the end, close the function and hit Enter to get the result.
=COUNTIFS(A2:A17,">="&E2,A2:A17,"<="&F2)
How this Formula Works
To understand this formula, you can break it down into two parts.
- In the first part, you have the condition to test for the cells greater than or equal to the date you have in cell E2, i.e., 4-Nov-2022.
- In the second part, you have the condition to test for the cells which are less than or equal to the date you have in cell F2, i.e., 4-Dec-2022.
So 15 cells have dates between the date range 4-Nov-2022 and 4-Dec-2022.
Note: Make sure to enter the greater than (>), less than (<), and equal (=) operators as text, surrounded by double quotation marks, and join the cell reference with an ampersand (&).
Use SUMPRODUCT to Count Between Dates
You can also use SUMPRODUCT to count dates between two dates, just like the following example:
=SUMPRODUCT(--(A2:A17<=F2),--(A2:A17>=E2))
Now let’s understand this formula step by step. But before that, you need to know that SUMPRODUCT can take an array in a single cell.
In the first part of the formula, you have a condition to check all the dates from the range. It will check which dates are less than or equal to the specified date. You can see it returns TRUE and FALSE.
After that, you have a double minus sign that converts these TRUE and FALSE into 1 and 0.
In the second part of the formula, you again have the condition to test. And it returns TRUE if that condition is met, else FALSE.
After that, the double minus sign converts TRUE and FALSE into 1 and 0.
At this point, you have two arrays. 1 means that the date in the range is between the specified date range.
SUMPRODUCT creates a product of these arrays and returns a single array, then sums the values from that array, which is the count of dates between the range.
Count Dates without the Start and End Date
The formula above includes both the lower and the upper date. If you only want the dates in between, remove the equal signs from both conditions:
=COUNTIFS(A2:A17,">"&E2,A2:A17,"<"&F2)
Count Dates That Have a Time
If the dates in your data also have a time, like 4-Dec-2022 12:00 PM, the “<=”&F2 condition skips them, because that value is greater than 4-Dec-2022 (which means midnight). To include the whole last day, use less than the upper date plus one:
=COUNTIFS(A2:A17,">="&E2,A2:A17,"<"&F2+1)
Count Dates Between Two Dates with Another Condition
COUNTIFS can take more pairs of ranges and criteria. Let’s say you have regions in column B and want to count the dates in the range only for “East”. Add a third pair to the formula:
=COUNTIFS(A2:A17,">="&E2,A2:A17,"<="&F2,B2:B17,"East")
And if you don’t want to use cells for the lower and upper dates, write them inside the formula with the DATE function. Don’t type a date as text like “4-Nov-2022” in the criteria, as it can be read differently on a computer with other date settings.
=COUNTIFS(A2:A17,">="&DATE(2022,11,4),A2:A17,"<="&DATE(2022,12,4))
Get the Number of Days Between Two Dates
If you don’t have a list of dates and only want to know how many days there are from one date to another, you don’t need COUNTIFS. Subtract the start date from the end date, or use the DAYS function (Excel 2013 and later). Both return 30 for 4-Nov-2022 and 4-Dec-2022:
=F2-E2
=DAYS(F2,E2)
Make sure to format the result cell as General or Number; otherwise, Excel may show it as a date. And to count only the working days (Monday to Friday), use NETWORKDAYS, which counts both the start and the end date and returns 21 here:
=NETWORKDAYS(E2,F2)
Frequently Asked Questions
Why does my COUNTIFS formula return 0?
The most common reason is that the dates in the range are text, not real dates. Text dates don’t match the conditions. Check them with the ISNUMBER function (a real date returns TRUE) and convert them with DATEVALUE or Text to Columns. Also check that the lower date is in the first condition and the upper date in the second.
Can I use COUNTIF instead of COUNTIFS?
COUNTIF takes only one condition, so you would need two of them: count the dates on or after the start date and subtract the dates after the end date.
=COUNTIF(A2:A17,">="&E2)-COUNTIF(A2:A17,">"&F2)
Does the formula count blank cells?
No. A blank cell isn’t greater than or equal to your start date, so both COUNTIFS and SUMPRODUCT leave it out.
ERROR RESULT #VALUE! AFTER THIS CONDITION
=COUNTIFS(Sheet1!N2:N1048576,”=”&D3,Sheet1!AR2:AR1024,”=”&”Vehicle Not Deliver Yet”)
The error in your formula occurs due to mismatched ranges. The ranges for the COUNTIFS function must be of the same size. In your case, the first range (Sheet1!N2:N1048576) is much larger than the second range (Sheet1!AR2:AR1024). Both ranges should have the same number of rows. To fix this, ensure that both ranges cover the same number of rows, like this:
=COUNTIFS(Sheet1!N2:N1024, "="&D3, Sheet1!AR2:AR1024, "="&"Vehicle Not Deliver Yet")This should work without an error, as both ranges now have 1024 rows. If your dataset goes beyond row 1024, adjust the ranges accordingly.