COUNTIFS With a Date Range in Excel

To use COUNTIFS with a date range, check the same date column twice: once for dates on or after the start, and once for dates on or before the end. The formula is =COUNTIFS(B2:B9,">="&F1,B2:B9,"<="&F2), and it counts every row whose date falls between the two, including both ends. This guide shows the pattern on a small order list, then adds months, extra conditions and dates with times.

How COUNTIFS counts a date range#

Excel stores every date as a whole number, counting days from 1 January 1900. So "dates between 1 March and 31 March" is really "numbers between two values", and COUNTIFS can check that with two conditions on one column.

=COUNTIFS(date_range, ">="&start_date, date_range, "<="&end_date)

A row is counted only when both conditions are true. The COUNTIFS function guide covers the general syntax. All the examples below use this order list in A1:C9:

Count dates between two dates with DATE#

To put the dates straight into the formula, build them with DATE(year, month, day). This counts the March orders:

=COUNTIFS(B2:B9,">="&DATE(2026,3,1),B2:B9,"<="&DATE(2026,3,31))

The result is 5 (A-102 to A-106). The 26 February and April orders fail one of the two conditions.

Two details make this work. The operator ">=" sits inside quotes, and & joins it to the date. And DATE always means the same day, whatever the computer's date settings are. Typing ">=3/1/2026" can mean 1 March on one computer and 3 January on another.

Use cells for the start and end dates#

Put the dates in cells when you want to change the range without editing the formula. Type the start date in F1 (1 Mar 2026) and the end date in F2 (31 Mar 2026), then:

=COUNTIFS(B2:B9,">="&F1,B2:B9,"<="&F2)

The result is again 5. Change F1 to 10 Mar 2026 and F2 to 5 Apr 2026, and it updates to 4 (15, 22 and 31 March, and 1 April).

Keep the cell reference outside the quotes. ">=F1" compares the dates with the letters F and 1, so it counts nothing and returns 0.

Count a whole month with EOMONTH#

For a monthly count, you only need the first day of the month. EOMONTH(start, 0) returns the last day of the same month, so you don't need to remember whether a month has 28, 30 or 31 days.

=COUNTIFS(B2:B9,">="&F1,B2:B9,"<="&EOMONTH(F1,0))

With 1 Mar 2026 in F1, this returns 5. To build a monthly summary, list the first day of each month in a column (1 Feb, 1 Mar, 1 Apr) and fill the formula down with the month cell as the start. You get 1, 5 and 2. Lock the date range with dollar signs first, as $B$2:$B$9.

For a single day rather than a range, see how to count a specific date with COUNTIF.

Add more conditions: date range plus region#

COUNTIFS accepts more range and criteria pairs, so you can add a region, status or name to the date range. To count North orders in March:

=COUNTIFS(B2:B9,">="&F1,B2:B9,"<="&F2,C2:C9,"North")

The result is 2 (A-103 and A-105). The North orders on 26 February and 1 April fall outside the dates. The guide to COUNTIFS and SUMIFS with multiple conditions shows how to build a full grid of counts by region and month.

To add up an amount column for the same dates instead of counting rows, swap COUNTIFS for SUMIFS and put the amount range first. See how to sum by date range.

Dates with times, open-ended ranges and rolling periods#

Real data often needs a slightly different version of the pattern.

• Dates with times. A value like 31 Mar 2026 14:30 is bigger than 31 Mar 2026 (which means midnight), so "<="&DATE(2026,3,31) leaves it out. Use "less than the next day" instead: "<"&DATE(2026,4,1).
• Before or after one date. Use a single condition. =COUNTIFS(B2:B9,"<"&DATE(2026,3,1)) returns 1, the February order.
• The last 30 days. Use TODAY: =COUNTIFS(B2:B9,">="&TODAY()-30,B2:B9,"<="&TODAY()). The count changes every day.
• One month across several years. COUNTIFS can't test the month alone. Use =SUMPRODUCT(--(MONTH(B2:B9)=3)) to count every March in the list. The comparison of COUNTIFS vs SUMPRODUCT explains when that's worth it.

Why your COUNTIFS date range returns 0 or the wrong count#

Most wrong counts come from the data, not the formula. Check these in order:

1. Dates stored as text. Imported dates often sit on the left of the cell and are skipped by COUNTIFS. Test with =ISNUMBER(B2). FALSE means text; convert the column with DATEVALUE or Data > Text to Columns.
2. The cell reference inside the quotes. Write ">="&F1, not ">=F1".
3. Start and end the wrong way round. If F1 is later than F2, no date can pass both conditions.
4. Times in the data. The last day is missed. Use "<" with the next day.
5. The wrong year. DATE(2025,3,1) against 2026 data returns 0.

If you only need one condition, COUNTIF is enough; the article on COUNTIF vs COUNTIFS compares the two.

Practise it#

Date criteria are a favourite in Excel tests because the & is easy to forget. Practise dates in criteria in a live spreadsheet: you count and total rows between two dates, build a monthly summary and drive the range from input cells, with every formula checked instantly. Then practise more pairs in the COUNTIFS and SUMIFS lesson. Start with the March count in the exercise on this page.