Min/Max help needed with a SumProduct

[JR]

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

 

[Domenic]

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="2LUN"),(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!

 

[JR]

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

 

[JR]

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

 

[Domenic]

Can you post the exact formula you're using?

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

 

[JR]

=SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000="2LUN"),('ACD
Dump'!D25001+'ACD Dump'!D25001)-('ACD Dump'!D15000+'ACD Dump'!E1:E5000))

 

[Domenic]

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=
"2LUN"),('ACD Dump'!D35000+'ACD Dump'!E3:E5000)-('ACD Dump'!D24999+
'ACD Dump'!E2:E4999))

 

[JR]

that worked

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=
"2LUN"),('ACD Dump'!D35000+'ACD Dump'!E3:E5000)-('ACD Dump'!D24999+
'ACD Dump'!E2:E4999))

 

[JR]

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: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

 

[Domenic]

Assumptions:

A11 contains the headers/labels

A232 contains the data

F2 contains the 'Associate' of interest

Formula:

=SUMPRODUCT(--(A2:A31=F2),--(B2:B31="1BRK"),(C3:C32+D332)-(C2:C31+D23
1))

Hope this helps!

 

[JR]

Domenic,

Here is the formula I am using:

=SUMPRODUCT(--(B2:B4999=G5),--(C2:C4999="1BRK"),(D35000+E3:E5000)-(D24999+E2:E4999))

However the formula is returning a value of 41:03, when the return should be
25:02.

Any thoughts?

Here is the data again:

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

 

[Domenic]

Okay, let's look at this step by step...

For the first break we have...

Associate 1BRK 2/8/06 9:27:51
Associate AVAILABLE 2/8/06 9:40:25

....giving us a total of 0:12:34.

For the second break we have...

Associate 1BRK 2/8/06 13:50:28
Associate AVAILABLE 2/8/06 14:06:38

....giving us a total of 0:16:10

Altogether, the total should be 0:28:44, which is what the formula
returns. Is this not correct?

 

[JR]

its returning:




0:41:03



Let me know what you need to help me. Also, I just learned this morning
that when I export the ACD data each associate that is listed will have a
header row. Will the multiple header rows make a difference with the formula?

 

[Domenic]

its returning:




0:41:03

Off hand, I don't know why that is. If you'd like I can send you a
sample file. If so, send me your email address. If you'd prefer, I can
take a look at your file.

Also, I just learned this morning
that when I export the ACD data each associate that is listed will have a
header row. Will the multiple header rows make a difference with the formula?

No, it shouldn't.