SUMProduct working most of the time...

[Guest]

I'm trying to determine the number of calls a 24/7 call center takes per
person in their first hour and their last hour. I'm trying to build logic for
the 7 different time ranges (pre-ot, early, 1st hr, btw hr, last hr, late,
post ot) and while QC'ing my work I found that some of the equations give
wrong info. Some agents stay overnight so their start time and end time are
on different reported dates.

Login ID Date Time Calls
Doe, J 2/28/2007 12:00 AM 12:30 AM 1
Doe, J 2/28/2007 12:30 AM 1:00 AM 0
Doe, J 2/28/2007 1:00 AM 1:30 AM 0
Doe, J 2/28/2007 1:30 AM 2:00 AM 0
Doe, J 2/28/2007 2:00 AM 2:30 AM 2
Doe, J 2/28/2007 2:30 AM 3:00 AM 0
Doe, J 2/28/2007 3:00 AM 3:30 AM 1 - last hr
Doe, J 2/28/2007 3:30 AM 4:00 AM 0 - last hr
Doe, J 2/28/2007 4:00 AM 4:30 AM 1 - late logout
Doe, J 2/28/2007 6:30 PM 7:00 PM 0 - early
Doe, J 2/28/2007 7:00 PM 7:30 PM 0 -1st hour
Doe, J 2/28/2007 7:30 PM 8:00 PM 1 -1st hour*
Doe, J 2/28/2007 8:00 PM 8:30 PM 1
Doe, J 2/28/2007 8:30 PM 9:00 PM 1
Doe, J 2/28/2007 9:00 PM 9:30 PM 0
Doe, J 2/28/2007 9:30 PM 10:00 PM 2
Doe, J 2/28/2007 10:00 PM 10:30 PM 1
Doe, J 2/28/2007 10:30 PM 11:00 PM 0
Doe, J 2/28/2007 11:00 PM 11:30 PM 1
Doe, J 2/28/2007 11:30 PM 12:00 AM 1*

=SUMPRODUCT(--(($A$2:$A$65536)=$L1),--(($B$2:$B$65536)=Q$1),--(($C$2:$C$65536>=$M1)),--(($D$2:$D$65536)<=$N1),(E$2:$E$65536))

where
A-L is the name comparison, B-Q is the date comparison, C-M is the 1st hour
start, D-N is the 1st hour end (plus one hour), E is sum of calls

For this particular agent the sum product produces 2 (the calls w/ *). If I
change it to be D<N instead of D<=N it only counts the 11:30p-12:00p hour.
How to I correct this to get the right data?

FYI this equation works for 90% of the others.

 

[Guest]

Thanks for the idea Art. Unfortunately I want to make a generic formula that
applies to all agents--some of which actually have their last hour at 12:00.

I'm more concerned that the equation is built to search for a range of time
(between 7pm and 8pm) and is selecting the incorrect range (just 7:30pm and
12am). I'm wondering is there an issue with the numbers being out of order
(first hour is not the first set of numbers, it's actually in the middle of
the data)?

 

[Art Farrell]

Hi James,

This should help for the formula you show. I don't know what it will do for
your others. Set up another condition. In J1 copy your 12:00 AM figure. Then
in your SumProduct formula after --(($D$2:$D$65536)<=$N1),
add --(($D$2:$D$65536)<>$J1), .
This will give your first hour total of 1 instead of the 2 total.

CHORDially,
Art Farrell

 

[Art Farrell]

James,

Perhaps you could keep your original formula and change the last time that
you show in column D from 12:00 AM to 11:59 PM with the underlying entry of
23:59:59. This is actually the last second in 2/28/07. Making it 12:00 AM
means it is the start of the day in 2/29/07 and this is where the problem
occurs. The first entry you have in column C is 12:00 AM of 2/28/07 which is
correct since it is the start of that day.

I don't think the numbers are 'out of order' and the SumProduct formula is
working as it should. If you take the starting time as 12:00 AM and the
ending time as 11:59 PM in the formula you get a total of 13 calls as it
should be.

CHORDially,
Art Farrell