Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count overlapping date ranges in Excel and Google Sheets
Topic:COUNT & counting patternsExcel & Google Sheets
=SUMPRODUCT((A2:A7<=D2)*(B2:B7>=C2))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1List all date ranges with start dates in A2:A7 and end dates in B2:B7.
- 2Put the reference range you want to test against in C2 (start) and D2 (end) — for example, 2025-03-01 to 2025-03-15.
- 3In E2 enter =SUMPRODUCT((A2:A7<=D2)*(B2:B7>=C2)). This counts every row where start <= reference_end AND end >= reference_start, which is the standard overlap condition.
- 4To find which ranges overlap (not just count), use FILTER in Excel 365 or Sheets: =FILTER(A2:B7,(A2:A7<=D2)*(B2:B7>=C2)).
- 5To count overlaps at a single point in time (e.g. how many ranges are active on a specific date), use: =SUMPRODUCT((A2:A7<=D2)*(B2:B7>=D2)) where D2 is a single date.
Tips
- The overlap test (start1 <= end2 AND end1 >= start2) handles all overlap cases: partial overlap, full containment, and identical ranges.
- Ranges that merely touch at a boundary (one ends the day another starts) count as overlapping under <= and >=. Change to < and > for strict interior overlap only.
- This formula scales to any number of rows without adjustment — just extend the ranges.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I find the maximum number of simultaneous overlaps across all ranges?
For each range's start date, count how many other ranges are active on that date. The MAX of those counts is the peak concurrency. Build a helper column with SUMPRODUCT((A$2:A$7<=A2)*(B$2:B$7>=A2)) for each row.
Does this formula handle ranges spanning multiple years?
Yes — as long as dates are stored as real date values (not text), the <= and >= comparisons work correctly regardless of how far apart the dates are.
More on COUNT & counting patterns
See all →How-to
How to count how many of a weekday are in a month
Count the number of Mondays, Fridays, or any specific weekday occurring within a given month using SUMPRODUCT and WEEKDAY.
How-toHow to build a frequency distribution
Build a frequency distribution in Excel or Google Sheets using FREQUENCY — count how many values fall in each bin with one array formula, no COUNTIF loop required.