Count Concurrent Events Based on Start / End DateTime

[NTA]

I have a report of phone calls listing Caller Name (column A), Call Start DateTime (column B), and Call End DateTime (column C). There is no other data available. The report is currently sorted by A (asc), B (asc), C (asc). These reports often exceed 5000 rows of data.

I would like column D to be populated with the total number of concurrent calls active at the time the call was placed (i.e. calls in progress, including the current call).

Example:
<table>
<tr><td>Caller Name</td><td>Call Start DateTime</td><td>Call End DateTime</td><td>Concurrent Calls at Start</td></tr><tr><td>Angela</td><td>2/1/2005 13:00</td><td>2/1/2005 17:00</td><td>2</td></tr><tr><td>Bob</td><td>2/1/2005 10:00</td><td>2/1/2005 12:00</td><td>1</td></tr><tr><td>Charlie</td><td>2/1/2005 11:00</td><td>2/1/2005 15:00</td><td>2</td></tr><tr><td>Diane</td><td>2/1/2005 14:00</td><td>2/1/2005 16:00</td><td>3</td></tr></table>

A formula (array formula?) solution is preferred over a VBA script.

Any help is appreciated!!!

-NTA

 

[Dutchy]

I believe you need a sumproduct formula

Put this in D1 and copy down the column as far as necessary

=SUMPRODUCT(--($B$1:$B$10000<=B1),--($C$1:$C$10000>=B1))

This will cope with up to 10000 rows, increase if necessary

 

[NTA]

Impressive! Had to remind myself to use the CTRL+SHIFT+ENTER to enter as an array formula, but once I did that it was GREAT!

Now I'll be spending my weekend trying to figure out the SUMPRODUCT formula - nice trick! Any help translating that formula into English?

Thank you!

-NTA

 

[fairwinds]

Hi,

you should not need to enter that formula with Ctrl + shift + enter.

 

[Dutchy]

You don't need CTRL+SHIFT+ENTER, although it uses arrays, it isn't an "array formula" in that sense.

All it does, effectively, is count the rows where the start time (in B) is less than or equal to B1 AND the end time (in C) is also greater than or equal to B1, i.e. the number of calls taking place at time B1.

You may wish to have D blank if there is no corresponding data in B, in which case you can modify the formula to

=IF(B1="","",SUMPRODUCT(--($B$1:$B$10000<=B1),--($C$1:$C$10000>=B1)))

 

[NTA]

Ooops... my mistake on the CTRL+SHIFT+ENTER. Initially I pasted the formula as-is into $D$2 ($D$1 has the column header). That didn't work for obvious reasons When I updated the formula for use in row 2 I also used the CTRL+SHIFT+ENTER, which is apparently not required.

I'm glad you all are thinking more clearly than I on Friday evening!

Thanks for the explanations!

-NTA