| 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)) |
| 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 | |||
Hybrid Mode
