Detect Overlapping Exclusive Windows in the Same Territory

Two rows in your avails file. Both exclusive. Same territory. Same right type. Overlapping dates. Different licensees. That’s a contract you physically can’t fulfill — the moment you license exclusively to Licensee A from July to December, you legally cannot license exclusively to Licensee B from October to March. Either one grant gets cancelled or you pay a breach.

These overlaps sneak in when avails come from multiple source systems, or when a deal gets amended and the old row isn’t deleted. You need a detector that runs before you ship.

Restate the Problem Precisely

“Overlap” here means: two rows where

• Same Title (column A)
• Same Territory (column B)
• Same Right (column C)
• Both are Exclusive = Yes (column F)
• Date ranges overlap (Start A ≤ End B AND Start B ≤ End A)

The overlap rule (Start A ≤ End B AND Start B ≤ End A) is the standard set-theory check for date-range intersection. It’s the key to the detection formula.

The Detection Formula

Add a helper column to your avails. For row 2:

=IF(F2<>"Yes", "",
  IF(COUNTIFS(
    A:A, A2,
    B:B, B2,
    C:C, C2,
    F:F, "Yes",
    D:D, "<="&E2,
    E:E, ">="&D2
  ) > 1, "OVERLAP", ""))

Walking through:

• F2<>"Yes" — skip non-exclusive rows; they can’t cause exclusivity conflicts.
• COUNTIFS(A:A, A2, B:B, B2, C:C, C2, F:F, "Yes") — count rows matching Title, Territory, Right, and Exclusive=Yes.
• D:D, "<="&E2 — count rows where Start ≤ this row’s End.
• E:E, ">="&D2 — count rows where End ≥ this row’s Start.

Together: count rows that share all four keys AND whose date ranges overlap this row. If that count is > 1, this row overlaps another. Flag OVERLAP.

Red-fill the OVERLAP cells with conditional formatting. Sort by the flag column descending; overlapping pairs cluster together.

Review the Flagged Pairs

Every OVERLAP involves at least two rows (each row counts itself in the COUNTIFS plus at least one other). Work through them in pairs:

1. Legitimate amendment. The later row replaces the earlier. Delete or supersede the earlier row; mark the later as Status = Active.
2. Double booking. One grant is wrong. Escalate to business affairs to decide which gets cancelled.
3. Territory ambiguity. Both rows say EMEA but one means “EMEA excluding UK” and the other is UK-specific. Normalize territories first (Normalize Territory Codes) and re-run.

Edge Cases

• Exact-match rows. Two identical rows (same everything) are overlaps by this rule. Usually a duplicate-entry mistake; dedupe by key first.
• Adjacent-but-not-overlapping. Row A ends 2026-12-31, Row B starts 2027-01-01. Mathematically Start A ≤ End B (Dec 31 ≤ any) is TRUE but Start B ≤ End A is Jan 1 ≤ Dec 31 — FALSE. So the formula correctly does not flag adjacent windows. If you want stricter (no-same-day-continuation) checking, use < instead of <=.
• Hold-over grants with indefinite end dates. If End = blank or a sentinel date like 9999-12-31, the overlap check against it always returns TRUE. That’s correct: an indefinite-exclusive grant legitimately overlaps every future grant. Flag these for manual handling.

Performance

COUNTIFS over a 20,000-row avails file runs about 15 seconds in Sheets. If that’s too slow, replace COUNTIFS with a MATCH-based approach, or dedupe by a composite key first (Title|Territory|Right) and only run the full overlap check within each cluster. For Excel, array formulas are faster than COUNTIFS at scale — use SUMPRODUCT.

Gotchas

• Non-exclusive rows muddle the picture. The formula skips them because non-exclusive grants don’t legally conflict. But if your contract has “exclusive vs. an SVOD” (meaning exclusive against other SVOD, but TVOD is allowed), you need to expand the definition — the rights column matters for the conflict rule. Adjust the COUNTIFS to match on right groups, not exact right strings.
• Windowing exceptions. An overlap may be intentional — a 30-day promo window sits inside a larger season exclusive. Add an Override column for manually-approved overlaps so they don’t re-flag every time.
• Cross-title crossovers. A franchise bundle (“Star Wars — Original Trilogy”) may cover three titles. If your avails have one row per film, bundle grants don’t overlap per-row but do overlap per-contract. That’s out of scope for this formula — handle via bundle-resolution logic upstream.

If You Need the Full Workflow

Validate an Avails Sheet Against IFTA + EMA Rules covers the full seven-check avails validator, of which overlap detection is one. Detect Territory & Rights Conflicts is the windowing-focused companion — same idea, broader rule set.

Download

The Avails Data Quality Scorecard includes overlap detection as rule #8 (extended set), using the exact COUNTIFS formula above on the generic source sheet.