|
Min/Max help needed with a SumProduct
Hello Excel Yodas
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 |
Min/Max help needed with a SumProduct
Assumptions:
A1:D1 contains the headers/labels A2:D30 contains the data Formulas: First logon time... =MIN(IF(A2:A30=F2,C2:C30+D2:D30)) ....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+D3:D30)-(C2:C29+D2:D2 9)) ....and format cell as h:mm:ss Last logoff time... =MAX(IF(A2:A30=F2,C2:C30+D2:D30)) ....confirmed with CONTROL+SHIFT+ENTER, and format cell as... m/d/yyyy h:mm:ss Hope this helps! In article , "JR" wrote: Hello Excel Yodas 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 |
Min/Max help needed with a SumProduct
Unfortunately, none of the formulas seem to work. Not sure what else to do.
Thanks for the help. "Domenic" wrote: Assumptions: A1:D1 contains the headers/labels A2:D30 contains the data Formulas: First logon time... =MIN(IF(A2:A30=F2,C2:C30+D2:D30)) ....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+D3:D30)-(C2:C29+D2:D2 9)) ....and format cell as h:mm:ss Last logoff time... =MAX(IF(A2:A30=F2,C2:C30+D2:D30)) ....confirmed with CONTROL+SHIFT+ENTER, and format cell as... m/d/yyyy h:mm:ss Hope this helps! In article , "JR" wrote: 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 |
Min/Max help needed with a SumProduct
okay, I got the min/max to work, but hte lunch duration is still not working
- any other thoughts? "Domenic" wrote: Assumptions: A1:D1 contains the headers/labels A2:D30 contains the data Formulas: First logon time... =MIN(IF(A2:A30=F2,C2:C30+D2:D30)) ....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+D3:D30)-(C2:C29+D2:D2 9)) ....and format cell as h:mm:ss Last logoff time... =MAX(IF(A2:A30=F2,C2:C30+D2:D30)) ....confirmed with CONTROL+SHIFT+ENTER, and format cell as... m/d/yyyy h:mm:ss Hope this helps! In article , "JR" wrote: 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 |
Min/Max help needed with a SumProduct
Can you post the exact formula you're using?
In article , "JR" wrote: okay, I got the min/max to work, but hte lunch duration is still not working - any other thoughts? "Domenic" wrote: Assumptions: A1:D1 contains the headers/labels A2:D30 contains the data Formulas: First logon time... =MIN(IF(A2:A30=F2,C2:C30+D2:D30)) ....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+D3:D30)-(C2:C29+D2:D2 9)) ....and format cell as h:mm:ss Last logoff time... =MAX(IF(A2:A30=F2,C2:C30+D2:D30)) ....confirmed with CONTROL+SHIFT+ENTER, and format cell as... m/d/yyyy h:mm:ss Hope this helps! In article , "JR" wrote: 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 |
Min/Max help needed with a SumProduct
=SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000="2LUN"),('ACD
Dump'!D2:D5001+'ACD Dump'!D2:D5001)-('ACD Dump'!D1:D5000+'ACD Dump'!E1:E5000)) "Domenic" wrote: Can you post the exact formula you're using? In article , "JR" wrote: okay, I got the min/max to work, but hte lunch duration is still not working - any other thoughts? "Domenic" wrote: Assumptions: A1:D1 contains the headers/labels A2:D30 contains the data Formulas: First logon time... =MIN(IF(A2:A30=F2,C2:C30+D2:D30)) ....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+D3:D30)-(C2:C29+D2:D2 9)) ....and format cell as h:mm:ss Last logoff time... =MAX(IF(A2:A30=F2,C2:C30+D2:D30)) ....confirmed with CONTROL+SHIFT+ENTER, and format cell as... m/d/yyyy h:mm:ss Hope this helps! In article , "JR" wrote: 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 |
Min/Max help needed with a SumProduct
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'!D3:D5000+'ACD Dump'!E3:E5000)-('ACD Dump'!D2:D4999+ 'ACD Dump'!E2:E4999)) In article , "JR" wrote: =SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000="2LUN"),('ACD Dump'!D2:D5001+'ACD Dump'!D2:D5001)-('ACD Dump'!D1:D5000+'ACD Dump'!E1:E5000)) |
Min/Max help needed with a SumProduct
that worked
"Domenic" wrote: 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'!D3:D5000+'ACD Dump'!E3:E5000)-('ACD Dump'!D2:D4999+ 'ACD Dump'!E2:E4999)) In article , "JR" wrote: =SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000="2LUN"),('ACD Dump'!D2:D5001+'ACD Dump'!D2:D5001)-('ACD Dump'!D1:D5000+'ACD Dump'!E1:E5000)) |
Min/Max help needed with a SumProduct
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" wrote: 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'!D3:D5000+'ACD Dump'!E3:E5000)-('ACD Dump'!D2:D4999+ 'ACD Dump'!E2:E4999)) In article , "JR" wrote: =SUMPRODUCT(--('ACD Dump'!B1:B5000=B3),--('ACD Dump'!C1:C5000="2LUN"),('ACD Dump'!D2:D5001+'ACD Dump'!D2:D5001)-('ACD Dump'!D1:D5000+'ACD Dump'!E1:E5000)) |
Min/Max help needed with a SumProduct
Assumptions:
A1:D1 contains the headers/labels A2:D32 contains the data F2 contains the 'Associate' of interest Formula: =SUMPRODUCT(--(A2:A31=F2),--(B2:B31="1BRK"),(C3:C32+D3:D32)-(C2:C31+D2:D3 1)) Hope this helps! In article , "JR" wrote: 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 |
Min/Max help needed with a SumProduct
Domenic,
Here is the formula I am using: =SUMPRODUCT(--(B2:B4999=G5),--(C2:C4999="1BRK"),(D3:D5000+E3:E5000)-(D2:D4999+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" wrote: Assumptions: A1:D1 contains the headers/labels A2:D32 contains the data F2 contains the 'Associate' of interest Formula: =SUMPRODUCT(--(A2:A31=F2),--(B2:B31="1BRK"),(C3:C32+D3:D32)-(C2:C31+D2:D3 1)) Hope this helps! In article , "JR" wrote: 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 |
Min/Max help needed with a SumProduct
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? In article , "JR" wrote: Domenic, Here is the formula I am using: =SUMPRODUCT(--(B2:B4999=G5),--(C2:C4999="1BRK"),(D3:D5000+E3:E5000)-(D2:D4999+ 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" wrote: Assumptions: A1:D1 contains the headers/labels A2:D32 contains the data F2 contains the 'Associate' of interest Formula: =SUMPRODUCT(--(A2:A31=F2),--(B2:B31="1BRK"),(C3:C32+D3:D32)-(C2:C31+D2:D3 1)) Hope this helps! In article , "JR" wrote: 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 |
Min/Max help needed with a SumProduct
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" wrote: 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? In article , "JR" wrote: Domenic, Here is the formula I am using: =SUMPRODUCT(--(B2:B4999=G5),--(C2:C4999="1BRK"),(D3:D5000+E3:E5000)-(D2:D4999+ 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" wrote: Assumptions: A1:D1 contains the headers/labels A2:D32 contains the data F2 contains the 'Associate' of interest Formula: =SUMPRODUCT(--(A2:A31=F2),--(B2:B31="1BRK"),(C3:C32+D3:D32)-(C2:C31+D2:D3 1)) Hope this helps! In article , "JR" wrote: 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 |
Min/Max help needed with a SumProduct
In article ,
"JR" wrote: 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. |
| All times are GMT +1. The time now is 09:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com