01 / 10The Check Compared Each Row With the One Above

Date FunctionsCOUNTIFSData Quality

Find Overlapping Date Ranges in Excel With COUNTIFS

Two Families Arrived at Gull Cottage With Keys to the Same Door, Because the Clash Check Only Compared Each Booking With the Row Above

11 Nov 20269 min read

UsesCOUNTIFSAND, OR, NOTFILTERMIN, MAX

Saltmarsh Cottages lets three holiday cottages and checks every booking for clashes before confirming it. On 15 August two parties arrived at Gull Cottage with keys to the same door. The clash column had said FALSE on the booking that caused it.

BookingCottageStayCheck saidIn fact
BK-304Gull8 to 22 AugFALSEClashes with BK-308
BK-308Gull15 to 19 AugFALSEClashes with BK-304
BK-312Gull29 Aug to 5 SepTRUEChangeover, no clash

Scope. The COUNTIFS, COUNTIF, AND, IF, MIN and MAX formulas work in every version from Excel 2007. FILTER needs Excel 2021 or Microsoft 365.

Departure dates here are checkout days: the guest leaves that morning and the next guest can arrive that afternoon.

01The Check Compared Each Row With the One Above

The bookings sheet lists twelve August stays in A2:E13, sorted by arrival date across all three cottages. Column F was the clash check. In F3, filled to F13:

=AND(B3=B2,C3<=D2)

August Bookings and the Clash Check

Saltmarsh Cottages' twelve August bookings in A1:H13, sorted by arrival date across three cottages. F is the old check against the row above. G counts clashes with <= and >=, which treats every changeover day as a clash. H counts them with < and >, the correct test.

ABCDEFGH
1
Booking
Cottage
Arrive
Depart
Nights
Row-above check
Clashes (<= >=)
Clashes (< >)
2
BK-301
Heron
01/08/2026
08/08/2026
7
1
0
3
BK-302
Gull
01/08/2026
08/08/2026
7
FALSE
1
0
4
BK-303
Puffin
03/08/2026
10/08/2026
7
FALSE
1
0
5
BK-304
Gull
08/08/2026
22/08/2026
14
FALSE
3
1
6
BK-305
Heron
08/08/2026
15/08/2026
7
FALSE
2
0
7
BK-306
Puffin
10/08/2026
17/08/2026
7
FALSE
2
0
8
BK-307
Heron
15/08/2026
22/08/2026
7
FALSE
2
0
9
BK-308
Gull
15/08/2026
19/08/2026
4
FALSE
1
1
10
BK-309
Puffin
17/08/2026
24/08/2026
7
FALSE
1
0
11
BK-310
Heron
22/08/2026
29/08/2026
7
FALSE
1
0
12
BK-311
Gull
22/08/2026
29/08/2026
7
FALSE
2
0
13
BK-312
Gull
29/08/2026
05/09/2026
7
TRUE
1
0

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

It asks two things: is this the same cottage as the row above, and does it arrive before that booking leaves? Ten rows say FALSE and F13 says TRUE.

The Haldane party's four nights at Gull, BK-308 in row 9, said FALSE. The row above is a Heron booking, so the first test failed and the check looked no further. The Ferris family's 14-night let was four rows up, in row 5.

Saltmarsh refunded the Haldanes' £760 and paid £1,140 for a hotel. The one TRUE, on BK-312, was a changeover day, and nobody had acted on it either.

Scenario: Enter the twelve bookings in A2:E13 and =AND(B3=B2,C3<=D2) in F3, filled to F13. Check that F9 says FALSE, even though BK-308 arrives at Gull while BK-304 is still there.

Try it in the grid

02One Neighbour Is Not Every Booking

A row-above check can only see one other booking. It works only when every clash sits in adjacent rows, and on a sheet of several cottages sorted by date, that is rarely true.

RowBookingArriveDepartCompared with
5BK-30408/0822/08BK-303, Puffin
9BK-30815/0819/08BK-307, Heron
12BK-31122/0829/08BK-310, Heron
13BK-31229/0805/09BK-311, Gull

Only one Gull booking was ever compared with another Gull booking. Every booking has to be tested against every other booking in the same cottage.

Sorting by cottage first does not save it. A long stay can overlap two short ones below it. The second short stay is compared with the first, which left in time, and the long stay above them is never checked.

Scenario: Sort A2:F13 by cottage and then arrival, and check that BK-308 now says TRUE. Then add a Gull booking for 20/08/2026 to 21/08/2026 under it and check that its row says FALSE.

Try it in the grid

03Two Stays Clash When Each Starts Before the Other Ends

Every overlap test reduces to one rule. Two stays clash when each one starts before the other one ends. Nothing else matters: which arrives first, which is longer, or whether one sits wholly inside the other.

For BK-308 against BK-304, both halves hold:

=AND(C9<D5,C5<D9)

15 August is before 22 August, and 8 August is before 19 August, so it returns TRUE. Swap the two bookings round and the answer is the same.

A test that only asks whether one arrival falls inside the other stay is half of this rule. It flags the later booking of a pair and leaves the earlier one looking clear.

The same four dates measure how bad a clash is. The shared nights are the earlier departure minus the later arrival, floored at zero:

=MAX(0,MIN(D9,D5)-MAX(C9,C5))

It returns 4: the Haldanes' whole stay sat inside the Ferris let. Two stays that only touch on a changeover day return 0.

Scenario: Enter =AND(C9<D5,C5<D9) in J2 and check that it returns TRUE. Then enter =MAX(0,MIN(D9,D5)-MAX(C9,C5)) in J3 and check that it returns 4.

Try it in the grid

04Count Every Clash for Every Row With COUNTIFS

COUNTIFS applies the rule to every other booking at once. Count the bookings in the same cottage that arrive before this one leaves and leave after this one arrives. In H2, filled to H13:

=COUNTIFS(B:B,B2,C:C,"<"&D2,D:D,">"&C2)-1

The -1 removes the booking itself, which always meets both conditions. The header in row 1 is text, so it never matches a date criterion.

H5 and H9 return 1, for BK-304 and BK-308, and every other row returns 0. Both halves of the clash are flagged, so whoever opens either booking sees it.

BookingArriveDepartRow-aboveCOUNTIFS
BK-30201/0808/08FALSE0
BK-30408/0822/08FALSE1
BK-30815/0819/08FALSE1
BK-31122/0829/08FALSE0
BK-31229/0805/09TRUE0

One cell at the top of the sheet totals it: =COUNTIF(H2:H13,">0") returns 2, the number of bookings caught in a clash.

Scenario: Enter the COUNTIFS formula in H2 and fill it to H13. Check that H5 and H9 return 1 and the other ten rows return 0, then enter =COUNTIF(H2:H13,">0") in H15.

Try it in the grid

05Departure Day Is Free: Use < and >

A guest who leaves on the 22nd and one who arrives on the 22nd do not clash. The departure date is the changeover day, not a night of the stay, so both comparisons must be strict.

Swap in <= and >= and every changeover becomes a clash. In G2, filled to G13:

=COUNTIFS(B:B,B2,C:C,"<="&D2,D:D,">="&C2)-1

On this sheet all twelve bookings show at least one clash. BK-304 shows 3, because it touches BK-302 on its arrival day and BK-311 on its departure day.

BookingWith <= and >=With < and >
BK-30210
BK-30431
BK-30811
BK-31120
BK-31210

A column that flags every row gets ignored. That is how the old check's one TRUE went unread, and how a real clash hides among false ones.

If the end date is the last day in use, as with equipment hired until the end of a day, the rule flips. That day is taken, so <= and >= are right.

Decide which kind of date the column holds, and say so in its header: "Depart" or "Last night".

Scenario: Enter the <= version in G2 and fill it to G13. Check that no row returns 0, then compare G5 with H5: 3 against 1.

Try it in the grid

06Name the Clashing Booking With FILTER

A count says there is a clash; the desk needs to know with whom. In Excel 2021 or Microsoft 365, FILTER returns the other booking's reference. For BK-308 in row 9:

=FILTER(A2:A13,(B2:B13=B9)*(C2:C13<D9)*(D2:D13>C9)*(A2:A13<>A9),"None")

It returns BK-304. The four conditions are multiplied, so a row is kept only when all four are true. The last one drops the booking itself, as the -1 did in COUNTIFS.

Point it at row 5 and it returns BK-308. A booking that clashes with two others spills both references down the column, so give it room below.

Scenario: Enter the FILTER formula in J5 and check that it returns BK-304. Then point it at BK-302 in row 3 and check that it returns None.

Try it in the grid

07Test a New Request Before You Confirm It

The cheapest clash is the one never booked. Put the request in its own cells and count it against the whole sheet before saying yes. With the cottage in L2, arrival in M2 and departure in N2:

=COUNTIFS(B2:B13,L2,C2:C13,"<"&N2,D2:D13,">"&M2)

There is no -1 here, because the request is not in the list yet. Gull from 19/08/2026 to 22/08/2026 returns 1: the Haldanes have left, but the Ferris let is still running.

Wrap it for the desk in O2:

=IF(COUNTIFS(B2:B13,L2,C2:C13,"<"&N2,D2:D13,">"&M2),"Clash","Free")

Gull from 05/09/2026 to 12/09/2026 returns Free. BK-312 leaves that morning, and the strict > lets the new party in that afternoon.

Type the dates as dates. A request date typed as text can turn the criterion into a text comparison, and the request reads Free against a full cottage. Build it with =DATE(2026,8,19) if in doubt.

Scenario: Enter Gull, 19/08/2026 and 22/08/2026 in L2:N2 and the IF formula in O2. Check that it says Clash, then change the dates to 05/09/2026 and 12/09/2026 and check that it says Free.

Try it in the grid

08Five Traps

  1. Comparing each row with the row above. It sees one neighbour. A long stay further up is never checked, whether the sheet is sorted by date or by cottage.
  2. Testing only whether an arrival falls inside another stay. It flags the later booking and leaves the earlier one looking clear. Use the two-sided rule.
  3. Using <= and >= on checkout dates. Every changeover becomes a clash, and a column that flags every row stops being read.
  4. Forgetting the -1. Every booking overlaps itself, so without it every row shows at least 1.
  5. Dates stored as text. A text date never meets a date criterion, so that booking's clashes go uncounted. Check with =COUNT(C2:D13), which should return 24.

09Four Exercises

  1. Reproduce it. Build the sheet and confirm that F9 says FALSE and F13 says TRUE.
  2. Fix it. Add the COUNTIFS column and confirm that only BK-304 and BK-308 return 1.
  3. Break the changeover. Switch to <= and >= and count how many rows show a clash.
  4. Check a request. Test Gull from 12/08/2026 to 15/08/2026 and name the booking it clashes with.

What to Take Away

Every date on Saltmarsh's sheet was right. The check compared each booking with one neighbour, when a clash can come from any row in the same cottage.

Two stays clash when each starts before the other ends. Count that with COUNTIFS for every row, keep < and > so changeover days stay free, and test each request before you confirm it.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge