Countif("between start/end times)

[Ian Jones]

Sheet 1, Row 1, Column 2 - opening hours divide into 15min segments. B1 -
08:00, C2 - 08:15, D2 - 08:30, and so on.

Sheet 2 , Columns 1 and 2 - a list of each agents break times. Column A -
Start time (eg 10:00) and Column B - End time (eg 10:30).

Sheet 1, Row 2, Column 1 - summary titles. (eg Cell A2 - "Morning Break").

Problem: I need to summarise the number of agents away on, say, "Morning
Break" per 15 min segment.
So, B2 must count the number of people on "Morning Break" at 08:00, B3..the
number of people of people on morning break at 08:15, and so on, based on the
list of start/end times found on Sheet 2.

=Countif(Sheet2!A:A, Sheet1!B1) inserted into Sheet 1 cell B2 is not
sufficient as it counts the number of people whose breaks begin at 08:00 only.

Thanks in advance.

Ian

[kraljb]

=SUMPRODUCT(--(Sheet2!$A$3:$A$23<B1),--(Sheet2!$B$3:$B$23>B1))

B1 being the time you are looking to see how many are on break and Sheet2!A3:B23 being the list of break times.