software

Hello Excel Yoda’s

For me this is an impossibility, but I know for some of you the force will
guide you to the answer. The first table shows my data. Please keep in mind
that immediately following Associate 1 there will be associate 2, Associate
3, etc…

Here is how I would like to return the data;
A B C
D
Associate“first logon time”“Lunch Duration”“Last Logoff time”

The associate name will already be listed in the A: column, so the times
will likely need to match the A: entry. The total lunch time can be
calculated by taking the entry marked “2LUN” and subtracting that time from
the first “Available” time following the “2LUN”

Bottom line is I do not know how to write min/max to match a criteria with a
sumproduct. I know I ask a lot, but I really could use the help and
appreciate your effort.

A B C D
Name Event Type Date Start Time
Associate 1 LOGON 2/8/2006 6:58:54
Associate 1 LOGOFF 2/8/2006 6:59:03
Associate 1 LOGON 2/8/2006 6:59:06
Associate 1 LOGON 2/8/2006 6:59:07
Associate 1 LOGON 2/8/2006 6:59:14
Associate 1 8WRP 2/8/2006 8:41:20
Associate 1 AVAILABLE 2/8/2006 8:43:46
Associate 1 1BRK 2/8/2006 9:27:51
Associate 1 AVAILABLE 2/8/2006 9:40:25
Associate 1 8WRP 2/8/2006 10:05:09
Associate 1 AVAILABLE 2/8/2006 10:07:28
Associate 1 8WRP 2/8/2006 10:20:32
Associate 1 AVAILABLE 2/8/200 10:27:58
Associate 1 8WRP 2/8/2006 11:12:14
Associate 1 AVAILABLE 2/8/2006 11:27:46
Associate 1 2LUN 2/8/2006 11:27:48
Associate 1 AVAILABLE 2/8/2006 12:05:32
Associate 1 8WRP 2/8/2006 12:35:06
Associate 1 AVAILABLE 2/8/2006 13:06:47
Associate 1 4CST 2/8/2006 13:06:49
Associate 1 AVAILABLE 2/8/2006 13:09:33
Associate 1 9OTH 2/8/2006 13:29:06
Associate 1 AVAILABLE 2/8/2006 13:50:04
Associate 1 1BRK 2/8/2006 13:50:28
Associate 1 AVAILABLE 2/8/2006 14:06:38
Associate 1 LOGOFF 2/8/2006 15:28:44
Associate 1 UNAVAILABLE 2/8/2006 15:28:50
Associate 1 AVAILABLE 2/8/2006 15:31:50
Associate 1 LOGOFF 2/8/2006 15:31:54
Assumptions:

A11 contains the headers/labels

A230 contains the data

Formulas:

First logon time...

=MIN(IF(A2:A30=F2,C2:C30 D230))

....confirmed with CONTROL SHIFT ENTER, and format cell as...

m/d/yyyy h:mm:ss

Lunch duration...

=SUMPRODUCT(--(A2:A29=F2),--(B2:B29=quot;2LUNquot;),(C3:C30 D330)-(C2:C29 D22
9))

....and format cell as h:mm:ss

Last logoff time...

=MAX(IF(A2:A30=F2,C2:C30 D230))

....confirmed with CONTROL SHIFT ENTER, and format cell as...

m/d/yyyy h:mm:ss

Hope this helps!

In article gt;,
quot;JRquot; gt; wrote:

gt; Hello Excel Yoda’s
gt;
gt; For me this is an impossibility, but I know for some of you the force will
gt; guide you to the answer. The first table shows my data. Please keep in mind
gt; that immediately following Associate 1 there will be associate 2, Associate
gt; 3, etc…
gt;
gt; Here is how I would like to return the data;
gt; A B C
gt; D
gt; Associate“first logon time”“Lunch Duration”“Last Logoff time”
gt;
gt; The associate name will already be listed in the A: column, so the times
gt; will likely need to match the A: entry. The total lunch time can be
gt; calculated by taking the entry marked “2LUN” and subtracting that time from
gt; the first “Available” time following the “2LUN”
gt;
gt; Bottom line is I do not know how to write min/max to match a criteria with a
gt; sumproduct. I know I ask a lot, but I really could use the help and
gt; appreciate your effort.
gt;
gt; A B C D
gt; Name Event Type Date Start Time
gt; Associate 1 LOGON 2/8/2006 6:58:54
gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt; Associate 1 LOGON 2/8/2006 6:59:06
gt; Associate 1 LOGON 2/8/2006 6:59:07
gt; Associate 1 LOGON 2/8/2006 6:59:14
gt; Associate 1 8WRP 2/8/2006 8:41:20
gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt; Associate 1 1BRK 2/8/2006 9:27:51
gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt; Associate 1 8WRP 2/8/2006 10:05:09
gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt; Associate 1 8WRP 2/8/2006 10:20:32
gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt; Associate 1 8WRP 2/8/2006 11:12:14
gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt; Associate 1 2LUN 2/8/2006 11:27:48
gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt; Associate 1 8WRP 2/8/2006 12:35:06
gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt; Associate 1 4CST 2/8/2006 13:06:49
gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt; Associate 1 9OTH 2/8/2006 13:29:06
gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt; Associate 1 1BRK 2/8/2006 13:50:28
gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt; Associate 1 LOGOFF 2/8/2006 15:31:54

Unfortunately, none of the formulas seem to work. Not sure what else to do.
Thanks for the help.

quot;Domenicquot; wrote:

gt; Assumptions:
gt;
gt; A11 contains the headers/labels
gt;
gt; A230 contains the data
gt;
gt; Formulas:
gt;
gt; First logon time...
gt;
gt; =MIN(IF(A2:A30=F2,C2:C30 D230))
gt;
gt; ....confirmed with CONTROL SHIFT ENTER, and format cell as...
gt;
gt; m/d/yyyy h:mm:ss
gt;
gt; Lunch duration...
gt;
gt; =SUMPRODUCT(--(A2:A29=F2),--(B2:B29=quot;2LUNquot;),(C3:C30 D330)-(C2:C29 D22
gt; 9))
gt;
gt; ....and format cell as h:mm:ss
gt;
gt; Last logoff time...
gt;
gt; =MAX(IF(A2:A30=F2,C2:C30 D230))
gt;
gt; ....confirmed with CONTROL SHIFT ENTER, and format cell as...
gt;
gt; m/d/yyyy h:mm:ss
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;JRquot; gt; wrote:
gt;
gt; gt; Hello Excel Yoda’s
gt; gt;
gt; gt; For me this is an impossibility, but I know for some of you the force will
gt; gt; guide you to the answer. The first table shows my data. Please keep in mind
gt; gt; that immediately following Associate 1 there will be associate 2, Associate
gt; gt; 3, etc…
gt; gt;
gt; gt; Here is how I would like to return the data;
gt; gt; A B C
gt; gt; D
gt; gt; Associate“first logon time”“Lunch Duration”“Last Logoff time”
gt; gt;
gt; gt; The associate name will already be listed in the A: column, so the times
gt; gt; will likely need to match the A: entry. The total lunch time can be
gt; gt; calculated by taking the entry marked “2LUN” and subtracting that time from
gt; gt; the first “Available” time following the “2LUN”
gt; gt;
gt; gt; Bottom line is I do not know how to write min/max to match a criteria with a
gt; gt; sumproduct. I know I ask a lot, but I really could use the help and
gt; gt; appreciate your effort.
gt; gt;
gt; gt; A B C D
gt; gt; Name Event Type Date Start Time
gt; gt; Associate 1 LOGON 2/8/2006 6:58:54
gt; gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt; gt; Associate 1 LOGON 2/8/2006 6:59:06
gt; gt; Associate 1 LOGON 2/8/2006 6:59:07
gt; gt; Associate 1 LOGON 2/8/2006 6:59:14
gt; gt; Associate 1 8WRP 2/8/2006 8:41:20
gt; gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt; gt; Associate 1 1BRK 2/8/2006 9:27:51
gt; gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt; gt; Associate 1 8WRP 2/8/2006 10:05:09
gt; gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt; gt; Associate 1 8WRP 2/8/2006 10:20:32
gt; gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt; gt; Associate 1 8WRP 2/8/2006 11:12:14
gt; gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt; gt; Associate 1 2LUN 2/8/2006 11:27:48
gt; gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt; gt; Associate 1 8WRP 2/8/2006 12:35:06
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt; gt; Associate 1 4CST 2/8/2006 13:06:49
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt; gt; Associate 1 9OTH 2/8/2006 13:29:06
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt; gt; Associate 1 1BRK 2/8/2006 13:50:28
gt; gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt; gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt; gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt; gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt; gt; Associate 1 LOGOFF 2/8/2006 15:31:54
gt;

okay, I got the min/max to work, but hte lunch duration is still not working
- any other thoughts?

quot;Domenicquot; wrote:

gt; Assumptions:
gt;
gt; A11 contains the headers/labels
gt;
gt; A230 contains the data
gt;
gt; Formulas:
gt;
gt; First logon time...
gt;
gt; =MIN(IF(A2:A30=F2,C2:C30 D230))
gt;
gt; ....confirmed with CONTROL SHIFT ENTER, and format cell as...
gt;
gt; m/d/yyyy h:mm:ss
gt;
gt; Lunch duration...
gt;
gt; =SUMPRODUCT(--(A2:A29=F2),--(B2:B29=quot;2LUNquot;),(C3:C30 D330)-(C2:C29 D22
gt; 9))
gt;
gt; ....and format cell as h:mm:ss
gt;
gt; Last logoff time...
gt;
gt; =MAX(IF(A2:A30=F2,C2:C30 D230))
gt;
gt; ....confirmed with CONTROL SHIFT ENTER, and format cell as...
gt;
gt; m/d/yyyy h:mm:ss
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;JRquot; gt; wrote:
gt;
gt; gt; Hello Excel Yoda’s
gt; gt;
gt; gt; For me this is an impossibility, but I know for some of you the force will
gt; gt; guide you to the answer. The first table shows my data. Please keep in mind
gt; gt; that immediately following Associate 1 there will be associate 2, Associate
gt; gt; 3, etc…
gt; gt;
gt; gt; Here is how I would like to return the data;
gt; gt; A B C
gt; gt; D
gt; gt; Associate“first logon time”“Lunch Duration”“Last Logoff time”
gt; gt;
gt; gt; The associate name will already be listed in the A: column, so the times
gt; gt; will likely need to match the A: entry. The total lunch time can be
gt; gt; calculated by taking the entry marked “2LUN” and subtracting that time from
gt; gt; the first “Available” time following the “2LUN”
gt; gt;
gt; gt; Bottom line is I do not know how to write min/max to match a criteria with a
gt; gt; sumproduct. I know I ask a lot, but I really could use the help and
gt; gt; appreciate your effort.
gt; gt;
gt; gt; A B C D
gt; gt; Name Event Type Date Start Time
gt; gt; Associate 1 LOGON 2/8/2006 6:58:54
gt; gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt; gt; Associate 1 LOGON 2/8/2006 6:59:06
gt; gt; Associate 1 LOGON 2/8/2006 6:59:07
gt; gt; Associate 1 LOGON 2/8/2006 6:59:14
gt; gt; Associate 1 8WRP 2/8/2006 8:41:20
gt; gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt; gt; Associate 1 1BRK 2/8/2006 9:27:51
gt; gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt; gt; Associate 1 8WRP 2/8/2006 10:05:09
gt; gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt; gt; Associate 1 8WRP 2/8/2006 10:20:32
gt; gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt; gt; Associate 1 8WRP 2/8/2006 11:12:14
gt; gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt; gt; Associate 1 2LUN 2/8/2006 11:27:48
gt; gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt; gt; Associate 1 8WRP 2/8/2006 12:35:06
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt; gt; Associate 1 4CST 2/8/2006 13:06:49
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt; gt; Associate 1 9OTH 2/8/2006 13:29:06
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt; gt; Associate 1 1BRK 2/8/2006 13:50:28
gt; gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt; gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt; gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt; gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt; gt; Associate 1 LOGOFF 2/8/2006 15:31:54
gt;

Can you post the exact formula you're using?

In article gt;,
quot;JRquot; gt; wrote:

gt; okay, I got the min/max to work, but hte lunch duration is still not working
gt; - any other thoughts?
gt;
gt; quot;Domenicquot; wrote:
gt;
gt; gt; Assumptions:
gt; gt;
gt; gt; A11 contains the headers/labels
gt; gt;
gt; gt; A230 contains the data
gt; gt;
gt; gt; Formulas:
gt; gt;
gt; gt; First logon time...
gt; gt;
gt; gt; =MIN(IF(A2:A30=F2,C2:C30 D230))
gt; gt;
gt; gt; ....confirmed with CONTROL SHIFT ENTER, and format cell as...
gt; gt;
gt; gt; m/d/yyyy h:mm:ss
gt; gt;
gt; gt; Lunch duration...
gt; gt;
gt; gt; =SUMPRODUCT(--(A2:A29=F2),--(B2:B29=quot;2LUNquot;),(C3:C30 D330)-(C2:C29 D22
gt; gt; 9))
gt; gt;
gt; gt; ....and format cell as h:mm:ss
gt; gt;
gt; gt; Last logoff time...
gt; gt;
gt; gt; =MAX(IF(A2:A30=F2,C2:C30 D230))
gt; gt;
gt; gt; ....confirmed with CONTROL SHIFT ENTER, and format cell as...
gt; gt;
gt; gt; m/d/yyyy h:mm:ss
gt; gt;
gt; gt; Hope this helps!
gt; gt;
gt; gt; In article gt;,
gt; gt; quot;JRquot; gt; wrote:
gt; gt;
gt; gt; gt; Hello Excel Yoda‰菄s
gt; gt; gt;
gt; gt; gt; For me this is an impossibility, but I know for some of you the force
gt; gt; gt; will
gt; gt; gt; guide you to the answer. The first table shows my data. Please keep in
gt; gt; gt; mind
gt; gt; gt; that immediately following Associate 1 there will be associate 2,
gt; gt; gt; Associate
gt; gt; gt; 3, etc‰ۛ
gt; gt; gt;
gt; gt; gt; Here is how I would like to return the data;
gt; gt; gt; A B C
gt; gt; gt;
gt; gt; gt; D
gt; gt; gt; Associate‰裉first logon time‰۝���Lunch Duration�۝���Last Logoff
gt; gt; gt; time‰۝
gt; gt; gt;
gt; gt; gt; The associate name will already be listed in the A: column, so the times
gt; gt; gt; will likely need to match the A: entry. The total lunch time can be
gt; gt; gt; calculated by taking the entry marked ‰裉2LUN‰۝ and subtracting that
gt; gt; gt; time from
gt; gt; gt; the first ‰裉Available‰۝ time following the ���2LUN�۝
gt; gt; gt;
gt; gt; gt; Bottom line is I do not know how to write min/max to match a criteria
gt; gt; gt; with a
gt; gt; gt; sumproduct. I know I ask a lot, but I really could use the help and
gt; gt; gt; appreciate your effort.
gt; gt; gt;
gt; gt; gt; A B C D
gt; gt; gt; Name Event Type Date Start Time
gt; gt; gt; Associate 1 LOGON 2/8/2006 6:58:54
gt; gt; gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt; gt; gt; Associate 1 LOGON 2/8/2006 6:59:06
gt; gt; gt; Associate 1 LOGON 2/8/2006 6:59:07
gt; gt; gt; Associate 1 LOGON 2/8/2006 6:59:14
gt; gt; gt; Associate 1 8WRP 2/8/2006 8:41:20
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt; gt; gt; Associate 1 1BRK 2/8/2006 9:27:51
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt; gt; gt; Associate 1 8WRP 2/8/2006 10:05:09
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt; gt; gt; Associate 1 8WRP 2/8/2006 10:20:32
gt; gt; gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt; gt; gt; Associate 1 8WRP 2/8/2006 11:12:14
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt; gt; gt; Associate 1 2LUN 2/8/2006 11:27:48
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt; gt; gt; Associate 1 8WRP 2/8/2006 12:35:06
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt; gt; gt; Associate 1 4CST 2/8/2006 13:06:49
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt; gt; gt; Associate 1 9OTH 2/8/2006 13:29:06
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt; gt; gt; Associate 1 1BRK 2/8/2006 13:50:28
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt; gt; gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt; gt; gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt; gt; gt; Associate 1 LOGOFF 2/8/2006 15:31:54
gt; gt;

=SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000=quot;2LUNquot;),('ACD
Dump'!D25001 'ACD Dump'!D25001)-('ACD Dump'!D15000 'ACD Dump'!E1:E5000))

quot;Domenicquot; wrote:

gt; Can you post the exact formula you're using?
gt;
gt; In article gt;,
gt; quot;JRquot; gt; wrote:
gt;
gt; gt; okay, I got the min/max to work, but hte lunch duration is still not working
gt; gt; - any other thoughts?
gt; gt;
gt; gt; quot;Domenicquot; wrote:
gt; gt;
gt; gt; gt; Assumptions:
gt; gt; gt;
gt; gt; gt; A11 contains the headers/labels
gt; gt; gt;
gt; gt; gt; A230 contains the data
gt; gt; gt;
gt; gt; gt; Formulas:
gt; gt; gt;
gt; gt; gt; First logon time...
gt; gt; gt;
gt; gt; gt; =MIN(IF(A2:A30=F2,C2:C30 D230))
gt; gt; gt;
gt; gt; gt; ....confirmed with CONTROL SHIFT ENTER, and format cell as...
gt; gt; gt;
gt; gt; gt; m/d/yyyy h:mm:ss
gt; gt; gt;
gt; gt; gt; Lunch duration...
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(A2:A29=F2),--(B2:B29=quot;2LUNquot;),(C3:C30 D330)-(C2:C29 D22
gt; gt; gt; 9))
gt; gt; gt;
gt; gt; gt; ....and format cell as h:mm:ss
gt; gt; gt;
gt; gt; gt; Last logoff time...
gt; gt; gt;
gt; gt; gt; =MAX(IF(A2:A30=F2,C2:C30 D230))
gt; gt; gt;
gt; gt; gt; ....confirmed with CONTROL SHIFT ENTER, and format cell as...
gt; gt; gt;
gt; gt; gt; m/d/yyyy h:mm:ss
gt; gt; gt;
gt; gt; gt; Hope this helps!
gt; gt; gt;
gt; gt; gt; In article gt;,
gt; gt; gt; quot;JRquot; gt; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hello Excel Yoda‰菄s
gt; gt; gt; gt;
gt; gt; gt; gt; For me this is an impossibility, but I know for some of you the force
gt; gt; gt; gt; will
gt; gt; gt; gt; guide you to the answer. The first table shows my data. Please keep in
gt; gt; gt; gt; mind
gt; gt; gt; gt; that immediately following Associate 1 there will be associate 2,
gt; gt; gt; gt; Associate
gt; gt; gt; gt; 3, etc‰ۛ
gt; gt; gt; gt;
gt; gt; gt; gt; Here is how I would like to return the data;
gt; gt; gt; gt; A B C
gt; gt; gt; gt;
gt; gt; gt; gt; D
gt; gt; gt; gt; Associate‰裉first logon time‰۝‰ÛÏLunch Duration‰Û‰ÛÏLast Logoff
gt; gt; gt; gt; time‰۝
gt; gt; gt; gt;
gt; gt; gt; gt; The associate name will already be listed in the A: column, so the times
gt; gt; gt; gt; will likely need to match the A: entry. The total lunch time can be
gt; gt; gt; gt; calculated by taking the entry marked ‰裉2LUN‰۝ and subtracting that
gt; gt; gt; gt; time from
gt; gt; gt; gt; the first ‰裉Available‰۝ time following the ‰ÛÏ2LUN‰Û
gt; gt; gt; gt;
gt; gt; gt; gt; Bottom line is I do not know how to write min/max to match a criteria
gt; gt; gt; gt; with a
gt; gt; gt; gt; sumproduct. I know I ask a lot, but I really could use the help and
gt; gt; gt; gt; appreciate your effort.
gt; gt; gt; gt;
gt; gt; gt; gt; A B C D
gt; gt; gt; gt; Name Event Type Date Start Time
gt; gt; gt; gt; Associate 1 LOGON 2/8/2006 6:58:54
gt; gt; gt; gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt; gt; gt; gt; Associate 1 LOGON 2/8/2006 6:59:06
gt; gt; gt; gt; Associate 1 LOGON 2/8/2006 6:59:07
gt; gt; gt; gt; Associate 1 LOGON 2/8/2006 6:59:14
gt; gt; gt; gt; Associate 1 8WRP 2/8/2006 8:41:20
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt; gt; gt; gt; Associate 1 1BRK 2/8/2006 9:27:51
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt; gt; gt; gt; Associate 1 8WRP 2/8/2006 10:05:09
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt; gt; gt; gt; Associate 1 8WRP 2/8/2006 10:20:32
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt; gt; gt; gt; Associate 1 8WRP 2/8/2006 11:12:14
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt; gt; gt; gt; Associate 1 2LUN 2/8/2006 11:27:48
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt; gt; gt; gt; Associate 1 8WRP 2/8/2006 12:35:06
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt; gt; gt; gt; Associate 1 4CST 2/8/2006 13:06:49
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt; gt; gt; gt; Associate 1 9OTH 2/8/2006 13:29:06
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt; gt; gt; gt; Associate 1 1BRK 2/8/2006 13:50:28
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt; gt; gt; gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt; gt; gt; gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt; gt; gt; gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt; gt; gt; gt; Associate 1 LOGOFF 2/8/2006 15:31:54
gt; gt; gt;
gt;

Your formula contains an error. And, assuming that the first row
contains the headers/labels, it should not be included in the
references. So, if B2:E5000 contains your data, try...

=SUMPRODUCT(--('ACD Dump'!B2:B4999=B3),--('ACD Dump'!C2:C4999=
quot;2LUNquot;),('ACD Dump'!D35000 'ACD Dump'!E3:E5000)-('ACD Dump'!D24999
'ACD Dump'!E2:E4999))

In article gt;,
quot;JRquot; gt; wrote:

gt; =SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000=quot;2LUNquot;),('ACD
gt; Dump'!D25001 'ACD Dump'!D25001)-('ACD Dump'!D15000 'ACD Dump'!E1:E5000))

that worked

quot;Domenicquot; wrote:

gt; Your formula contains an error. And, assuming that the first row
gt; contains the headers/labels, it should not be included in the
gt; references. So, if B2:E5000 contains your data, try...
gt;
gt; =SUMPRODUCT(--('ACD Dump'!B2:B4999=B3),--('ACD Dump'!C2:C4999=
gt; quot;2LUNquot;),('ACD Dump'!D35000 'ACD Dump'!E3:E5000)-('ACD Dump'!D24999
gt; 'ACD Dump'!E2:E4999))
gt;
gt; In article gt;,
gt; quot;JRquot; gt; wrote:
gt;
gt; gt; =SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000=quot;2LUNquot;),('ACD
gt; gt; Dump'!D25001 'ACD Dump'!D25001)-('ACD Dump'!D15000 'ACD Dump'!E1:E5000))
gt;

Okay,

You saved my life. Now if your willing to jump back into the river to find
my wallet. Can you help me out by adding the total time the associate spend
on break. You will notice on the example I posted there are two breaks
(1BRK). Can you help with a formula that will Talley the total break time,
which is total between the 1BRK time and the time next to the available
immediately following the 1BRK

A B C D
Name Event Type Date Start Time
Associate 1 LOGON 2/8/2006 6:58:54
Associate 1 LOGOFF 2/8/2006 6:59:03
Associate 1 LOGON 2/8/2006 6:59:06
Associate 1 LOGON 2/8/2006 6:59:07
Associate 1 LOGON 2/8/2006 6:59:14
Associate 1 8WRP 2/8/2006 8:41:20
Associate 1 AVAILABLE 2/8/2006 8:43:46Associate 1 1BRK 2/8/2006 9:27:51
Associate 1 AVAILABLE 2/8/2006 9:40:25Associate 1 8WRP 2/8/2006 10:05:09
Associate 1 AVAILABLE 2/8/2006 10:07:28
Associate 1 8WRP 2/8/2006 10:20:32
Associate 1 AVAILABLE 2/8/200 10:27:58
Associate 1 8WRP 2/8/2006 11:12:14
Associate 1 AVAILABLE 2/8/2006 11:27:46
Associate 1 2LUN 2/8/2006 11:27:48
Associate 1 AVAILABLE 2/8/2006 12:05:32
Associate 1 8WRP 2/8/2006 12:35:06
Associate 1 AVAILABLE 2/8/2006 13:06:47
Associate 1 4CST 2/8/2006 13:06:49
Associate 1 AVAILABLE 2/8/2006 13:09:33
Associate 1 9OTH 2/8/2006 13:29:06
Associate 1 AVAILABLE 2/8/2006 13:50:04Associate 1 1BRK 2/8/2006 13:50:28
Associate 1 AVAILABLE 2/8/2006 14:06:38Associate 1 LOGOFF 2/8/2006 15:28:44
Associate 1 UNAVAILABLE 2/8/2006 15:28:50
Associate 1 AVAILABLE 2/8/2006 15:31:50
Associate 1 LOGOFF 2/8/2006 15:31:54quot;Domenicquot; wrote:

gt; Your formula contains an error. And, assuming that the first row
gt; contains the headers/labels, it should not be included in the
gt; references. So, if B2:E5000 contains your data, try...
gt;
gt; =SUMPRODUCT(--('ACD Dump'!B2:B4999=B3),--('ACD Dump'!C2:C4999=
gt; quot;2LUNquot;),('ACD Dump'!D35000 'ACD Dump'!E3:E5000)-('ACD Dump'!D24999
gt; 'ACD Dump'!E2:E4999))
gt;
gt; In article gt;,
gt; quot;JRquot; gt; wrote:
gt;
gt; gt; =SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000=quot;2LUNquot;),('ACD
gt; gt; Dump'!D25001 'ACD Dump'!D25001)-('ACD Dump'!D15000 'ACD Dump'!E1:E5000))
gt;

Assumptions:

A11 contains the headers/labels

A232 contains the data

F2 contains the 'Associate' of interest

Formula:

=SUMPRODUCT(--(A2:A31=F2),--(B2:B31=quot;1BRKquot;),(C3:C32 D332)-(C2:C31 D23
1))

Hope this helps!

In article gt;,
quot;JRquot; gt; wrote:

gt; Okay,
gt;
gt; You saved my life. Now if your willing to jump back into the river to find
gt; my wallet. Can you help me out by adding the total time the associate spend
gt; on break. You will notice on the example I posted there are two breaks
gt; (1BRK). Can you help with a formula that will Talley the total break time,
gt; which is total between the 1BRK time and the time next to the available
gt; immediately following the 1BRK
gt;
gt; A B C D
gt; Name Event Type Date Start Time
gt; Associate 1 LOGON 2/8/2006 6:58:54
gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt; Associate 1 LOGON 2/8/2006 6:59:06
gt; Associate 1 LOGON 2/8/2006 6:59:07
gt; Associate 1 LOGON 2/8/2006 6:59:14
gt; Associate 1 8WRP 2/8/2006 8:41:20
gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt;
gt;
gt; Associate 1 1BRK 2/8/2006 9:27:51
gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt;
gt;
gt; Associate 1 8WRP 2/8/2006 10:05:09
gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt; Associate 1 8WRP 2/8/2006 10:20:32
gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt; Associate 1 8WRP 2/8/2006 11:12:14
gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt; Associate 1 2LUN 2/8/2006 11:27:48
gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt; Associate 1 8WRP 2/8/2006 12:35:06
gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt; Associate 1 4CST 2/8/2006 13:06:49
gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt; Associate 1 9OTH 2/8/2006 13:29:06
gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt;
gt;
gt; Associate 1 1BRK 2/8/2006 13:50:28
gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt;
gt;
gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt; Associate 1 LOGOFF 2/8/2006 15:31:54