sumproduct help

[JR]

Hello,

I have a couple questions today. And let me thank you in advance for your
help – this is a great forum.

Question 1:
Below is a chart. What I need help with is matching the agent name (column
A) to the Event Type “Logon†(column B) and record the minimum time (from
column D) this event type was used (logon that is). I also need to have the
max time for “logoffâ€, but I assume I will replace the “Logon†and min in the
formula you provide.

Questions 2:
Matching the agents name (from column A) and matching “ACD Incoming†(from
column B) I need help with a formula that will count the number of times
Q10556, Q10557, Q10558 and Q10559 appear in the queue (column E).

Again I appreciate your time and help.

A B C D
E
Name Event Type Date Time Queue
Agent LOGON 2/20/2006 11:11:18 Q10556
Agent WRAPUP 2/20/2006 11:11:19
Agent LOGON 2/20/2006 11:11:24 Q10557
Agent WRAPUP 2/20/2006 11:11:25
Agent AVAILABLE 2/20/2006 11:11:29
Agent ACD INCOMING 2/20/2006 11:12:51 Q10556
Agent LOGOFF 2/20/2006 15:28:46 Q10556
Agent WRAPUP 2/20/2006 15:28:47
Agent WRAPUP 2/20/2006 15:28:49
Agent LOGOFF 2/20/2006 15:28:49 Q10557
Agent LOGOFF 2/20/2006 17:27:16 Q10556

 

[Domenic]

For the first part:

If your data spans more than 1 day...

=MIN(IF(A2:A12="Agent",IF(B2:B12="Logon",(C2:C12+D212))))

Otherwise, the following would suffice...

=MIN(IF(A2:A12="Agent",IF(B2:B12="Logon",D212)))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

For the second part:

=SUMPRODUCT(--(A2:A12="Agent"),--(B2:B12="ACD
Incoming"),--(E2:E12="Q10556"))

Hope this helps!

 

[Domenic]

With regards to the first part, I forgot to mention to format your cells
as 'Time'...

Hope this helps!