If statements in Vlookup - can it be done?

[Guest]

Hi i have a tedious job of taking clock-in data from a call center and
reporting back how many calls were taken in their first and last hour. I'm
currently doing this manually but there has to be a faster more effeicient
way to do this.

The data is in this format:

Name - Date - Time1 - Time2 - Calls - Staffed Time
J. Doe - 3/12 - 7:00 AM - 7:30 AM - 0 - 2:30 (logged in 2:30 min early)
J. Doe - 3/12 - 7:30 AM - 8:00 AM - 1 - 30:00
J. Doe - 3/12 - 8:00 AM - 8:30 AM - 3 - 30:00
etc.

I have another set of data with their name and shift time that I can compare
to. There are 400+ agents and the fun part is that they sometimes come in
early or stay late for OT. I would also like to break that data out into a
different cell. Note that I would not need to report how many calls were
taken between their first and last hour. Additionally if J. Doe took a call
in the 2:30min he was logged in early, that is also part of the first hour,
and the agents have different start times that include extended (ie
graveyard) hours (the start of their data is on one sheet and the other data
is on the second sheet with the next day shifts)

My thought was to do something similar to Vlookup, with if-then statements.
For example:
If Name=J.Doe and Time1 = Start_Time, then (report back the sum of calls)

I tried with Vlookup and it won't take an if statement in the formula. My
excel guru's around here are stumped too. Is there another function that will
produce the same result? The reported data will need to be a list of: agent
name, date, number of calls taken first hour, last hour.

Thanks

 

[Guest]

Look at SUMPRODUCT:

Perhaps something along these lines ...

=SUMPRODUCT(--(A1:A100)="Agent"),--(B1:B100>="StartTime"),--(B1:B100)<="EndTime"),(C1:C100))

where "Agent" is agent name (in Column A)
StartTime is Start of first hour (times in Column B)
EndTime is End of First hour
Column C contains the number of calls

Adjust ranges to suit

HTH

 

[Guest]

Thanks! I did this, changed a few things and it worked. Or worked for a while
with the sample data and now after I put the real data in I'm having an issue
with it returning incorrect data for one, and correct data for another.

Adams, M 2/27/2007 9:30 AM 10:00 AM 0 :11:28
Adams, M 2/27/2007 10:00 AM 10:30 AM 0 :30:00
Adams, M 2/27/2007 10:30 AM 11:00 AM 1 :30:00
Adams, M 2/27/2007 11:00 AM 11:30 AM 0 :30:00
etc.
Adams, M 2/27/2007 5:30 PM 6:00 PM 0 :30:00
Adams, M 2/27/2007 6:00 PM 6:30 PM 0 :30:00
Adams, M 2/27/2007 6:30 PM 7:00 PM 0 :30:00
Adams, M 2/27/2007 7:00 PM 7:30 PM 0 :03:05

Doe, J 2/27/2007 12:30 PM 1:00 PM 0 :01:45
Doe, J 2/27/2007 1:00 PM 1:30 PM 0 :30:00
Doe, J 2/27/2007 1:30 PM 2:00 PM 4 :30:00
Doe, J 2/27/2007 2:00 PM 2:30 PM 10 :30:00
Doe, J 2/27/2007 2:30 PM 3:00 PM 1 :30:00
etc.
Doe, J 2/27/2007 8:30 PM 9:00 PM 1 :30:00
Doe, J 2/27/2007 9:00 PM 9:30 PM 1 :30:00
Doe, J 2/27/2007 9:30 PM 10:00 PM 0 :30:00
Doe, J 2/27/2007 10:00 PM 10:30 PM 1 :00:23


Smith, T 2/27/2007 5:30 AM 6:00 AM 0 :01:40
Smith, T 2/27/2007 6:00 AM 6:30 AM 2 :30:00
Smith, T 2/27/2007 6:30 AM 7:00 AM 3 :30:00
Smith, T 2/27/2007 7:00 AM 7:30 AM 2 :30:00
etc.
Smith, T 2/27/2007 1:30 PM 2:00 PM 1 :30:00
Smith, T 2/27/2007 2:00 PM 2:30 PM 0 :30:00
Smith, T 2/27/2007 2:30 PM 3:00 PM 0 :30:00
Smith, T 2/27/2007 3:00 PM 3:30 PM 0 :08:56

Their shifts are and calls taken in their start-up hour
Adams, M 10:00 AM-7:00 PM Calls- 1 @ start, 0 @ end
Doe, J 1:00 PM-10:00 PM Calls- 4 @ start, 2 @ end
Smith, T 6:00 AM-3:00 PM Calls- 5 @ start, 0 @ end

My formula is:
=SUMPRODUCT(--((Sheet5!$A$2:$A$20000)=$A3),--((Sheet5!$B$2:$B$20000)=$O$1),--((Sheet5!$C$2:$C$20000>=$H3)),--((Sheet5!$D$2:$D$20000)<=$I3),(Sheet5!$E2:$E20000))

Where
A2:A20000 = A3 matches the names in the column of data to the specific agent
(I have 2 headers rows, hence the A3 and not A2).
B2:B20000 = O1 matches date in first header
C2:C20000 >= H3 matches start of their assigned shift to the beginning of
the 30min interval in the data and anything larger thru...
D220000 <= I3 matches end of their assigned shift to the end of the 30min
interval in the data and anything smaller than above...
D220000 - returns the sum of the calls in the data for the above listed
range.

Also H3 is a cells with a formula (vlookup on another sheet), and
I3=H3+TIME(1,0,0) to give it that one hour window.

So my data yields

Adams, M 1 (correct)
Doe, J 14 (wrong- grabs next interval's 10 calls)
Smith, T 3 (wrong- doesn't grab the first part of the interval--2 calls)

I'm stumped. If they were all the same error I could figure it out.

Help?