Count for every 'x' that falls into an interval within a given time range?

Question

I'm looking to get a headcount of people available every hour within a time frame.

Employee	Start Time	End Time
Emp name	8:30 AM	10:30 AM
Emp name	8:30 AM	10:45 AM
Emp name	8:00 AM	10:15 AM

If I were to take a count of employees available at 9:45 AM, I should get 3. Or maybe I want 15 minute intervals and want to know my headcount at 8:15, which would be 1.

Currently my solution is to make a table for every hour in a day using the following formula to add a tally for every hour an employee is available:

=IF(AND($G9>=R$1,$G9<S$1),1,IF(AND(R$1>$G9,R$1<$H9),1,""))

And i get something that looks like this:

Start Time	End Time		8:00 AM	9:00 AM	10:00 AM	11:00 AM
8:30 AM	10:30 AM			1	1

Finally I can pivot this or countif it to get my total.

Is there a more elegant solution to this? Preferably one that allows me to change the interval but still gives me an accurate count. Even better if I don't need to build out the interval table.

I'm open to using VBA or formulas.

Overall, what is this problem called? I'd like to look up a pythonic solution on my own time for some self study.

Answer 1

If every line represents a different employee, then just use COUNTIFS:

=COUNTIFS(B:B,"<="&E2,C:C,">="&E2)