# Use excel to count number of concurrent external calls

I am contemplating switching my company phone service from a traditional pri circuit to a virtual sip trunk.  I need to determine the maximum number of calls at any one time.  I have a spreadsheet (see attached) that contains start_timestamp, answer_timestamp, end_timestamp, destination, billsec, duration, and direction.  The time stamps are down to the second.  I'd like to calculate how many simultaneous calls are happening at any given time so I can order the correct amount of telephone lines from the phone company.  To complicate things, i do not need to count calls that are between internal 4 digit extensions.  For example, i do not need to count a call between 3876 and 3954.  I only need to count a call if either the caller or destination is an external number.  Ive googled around but cant find anything that does specifically this. Thank you!!!
cdr.xls
Patrick Matthews

You wrote:

To complicate things, i do not need to count calls that are between internal 4 digit extensions.  For example, i do not need to count a call between 3876 and 3954.  I only need to count a call if either the caller or destination is an external number.

I am looking at your sample file now, and it is far from clear what calls "internal".  Can you clarify that, please?

:)
Assuming that you mean to count only those calls with entries in Columns D and/or E...

1) In another area on that worksheet (or even on a separate worksheet), make a column of evenly spaced date/time values.  I spaced mine 20 seconds apart, but 10 or 15 seconds would do as well.  For example:

Time
3/22/13 8:00:00 AM
3/22/13 8:00:20 AM
3/22/13 8:00:40 AM
3/22/13 8:01:00 AM
3/22/13 8:01:20 AM
3/22/13 8:01:40 AM
3/22/13 8:02:00 AM

et cetera

2) Use a formula to count the number of calls active at each time:

=SUMPRODUCT((\$A\$2:\$A\$3566<=K2)*(\$C\$2:\$C\$3566>=K2)*(LEN(\$D\$2:\$D\$3566&\$E\$2:\$E\$3566)>0))

3) The max of that column is an estimate of the maximum number of concurrent calls.  The shorter you make the interval, the better the estimate gets

Please see this file for an example:

Q-28074368.xls

I am reviewing my original attachment.  I must have inadvertently messed up the internal calls while i was masking the phone #s.  Hold on