sumproduct help
 

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

sumproduct help
 

For the first part:

If your data spans more than 1 day...

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

Otherwise, the following would suffice...

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

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!

In article ,
"JR" wrote:

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

sumproduct help
 

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

Hope this helps!

In article ,
Domenic wrote:

For the first part:

If your data spans more than 1 day...

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

Otherwise, the following would suffice...

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

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!