| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct Help Needed | Excel Discussion (Misc queries) | |||
| SUMPRODUCT help needed | Excel Worksheet Functions | |||
| Sumproduct help needed! | Excel Worksheet Functions | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
