Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Employee Work Time - Don't Count Duplicates
From a database of customer training appointments, I need to count the time
each employee works each day. One customer is scheduled with an employee at a time. The current formula double-counts overlapping time periods. Current formula: =(SUMPRODUCT((Emp=382)*(Date=4/1/2006)*(Duration)))/60 The data looks like: Emp Date Start time Min. AptID 382 4/1/06 9:00:00 AM 60 123 382 4/1/06 9:00:00 AM 60 123 382 4/106 1:00:00 PM 90 223 382 4/1/06 2:00:00 PM 90 223 382 4/1/06 4:00:00 PM 60 333 382 4/1/06 4:00:00 PM 60 333 210 Total Minutes What formula would keep from double counting the same time period by ignoring subsequent records with the same AptID? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Employee Work Time - Don't Count Duplicates
This should work, but maybe someone can improve on it. Plus, you'd need to
add two columns, F & G (assuming yours started in cell A1 and spanned columns A - E). This solution assumes that your "AptID" data might be reused from one day or employee to the next. If that's not true the approach could be simpler. Add a column F called "ID" which creates a "unique identifier" by stringing together the employee number, date, and AptID. In cell F2 type the following: =A2&B2&E2. You can copy that down to all the other rows. Next add a column G called "UniqueApt" which shows the Minutes again, but only if that appointment is "unique" (i.e., not the same employee and date and AptID). In cell G2 type: =IF(COUNTIF(F$1:F1,F2)=0,D2,""). Copy that down to all the other rows. Now your sumproduct would reference the new column G which shows the minutes only when the appt is unique: =SUMPRODUCT(--(A2:A100=382),--(B2:B100=4/1/2006),(G2:G100)) Hope that helps. Write back if stuck. "J" wrote: From a database of customer training appointments, I need to count the time each employee works each day. One customer is scheduled with an employee at a time. The current formula double-counts overlapping time periods. Current formula: =(SUMPRODUCT((Emp=382)*(Date=4/1/2006)*(Duration)))/60 The data looks like: Emp Date Start time Min. AptID 382 4/1/06 9:00:00 AM 60 123 382 4/1/06 9:00:00 AM 60 123 382 4/106 1:00:00 PM 90 223 382 4/1/06 2:00:00 PM 90 223 382 4/1/06 4:00:00 PM 60 333 382 4/1/06 4:00:00 PM 60 333 210 Total Minutes What formula would keep from double counting the same time period by ignoring subsequent records with the same AptID? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Employee Work Time - Don't Count Duplicates
Andy,
I did not explain sufficiently. The AptID is unique to the date/time period but more than one record with the same AptID can exist in the same day. This same AptID will not be utilized ever again. I want to only include one of the records with a specific AptID but no others. Hope this helps. Thanks for the help! "andy62" wrote: This should work, but maybe someone can improve on it. Plus, you'd need to add two columns, F & G (assuming yours started in cell A1 and spanned columns A - E). This solution assumes that your "AptID" data might be reused from one day or employee to the next. If that's not true the approach could be simpler. Add a column F called "ID" which creates a "unique identifier" by stringing together the employee number, date, and AptID. In cell F2 type the following: =A2&B2&E2. You can copy that down to all the other rows. Next add a column G called "UniqueApt" which shows the Minutes again, but only if that appointment is "unique" (i.e., not the same employee and date and AptID). In cell G2 type: =IF(COUNTIF(F$1:F1,F2)=0,D2,""). Copy that down to all the other rows. Now your sumproduct would reference the new column G which shows the minutes only when the appt is unique: =SUMPRODUCT(--(A2:A100=382),--(B2:B100=4/1/2006),(G2:G100)) Hope that helps. Write back if stuck. "J" wrote: From a database of customer training appointments, I need to count the time each employee works each day. One customer is scheduled with an employee at a time. The current formula double-counts overlapping time periods. Current formula: =(SUMPRODUCT((Emp=382)*(Date=4/1/2006)*(Duration)))/60 The data looks like: Emp Date Start time Min. AptID 382 4/1/06 9:00:00 AM 60 123 382 4/1/06 9:00:00 AM 60 123 382 4/106 1:00:00 PM 90 223 382 4/1/06 2:00:00 PM 90 223 382 4/1/06 4:00:00 PM 60 333 382 4/1/06 4:00:00 PM 60 333 210 Total Minutes What formula would keep from double counting the same time period by ignoring subsequent records with the same AptID? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Employee Work Time - Don't Count Duplicates
If you're saying that an AptID wouldn't be used again for another employee or
on another day, then you don't need my "ID" column. Here's the approach again with only one added column. (I still would bet someone could construct a formula to calculate what you want without adding a column, but we'll see if we get anything). My prior assumptions about your data being in columns A-E still apply: Add a column F called "UniqueApt" which shows the Minutes again, but only if that appointment is "unique" (i.e., not the same AptID as any prior row). In cell F2 type: =IF(COUNTIF(E$1:E1,E2)=0,D2,""). Copy that down to all the other rows. Now your new "Minutes" column shows only unique entries. To total them for an employee on a given day, the formula would become: =SUMPRODUCT(--(A2:A10=382),--(B2:B10=DATE(2006,4,1)),(F2:F10)) Matrix or all subtotals: Instead of embedding the date function and employee number in the formula, you could use references to the row/column headers of a matrix of all employees vs all dates. Then just copy the formula to all the other cells in the matrix to fill it out with all your totals. Be careful to anchor the cell references appropriately so you can copy the formula in two dimensions but still have it point to the right date and employee cells. I can help if needed . . . "J" wrote: Andy, I did not explain sufficiently. The AptID is unique to the date/time period but more than one record with the same AptID can exist in the same day. This same AptID will not be utilized ever again. I want to only include one of the records with a specific AptID but no others. Hope this helps. Thanks for the help! "andy62" wrote: This should work, but maybe someone can improve on it. Plus, you'd need to add two columns, F & G (assuming yours started in cell A1 and spanned columns A - E). This solution assumes that your "AptID" data might be reused from one day or employee to the next. If that's not true the approach could be simpler. Add a column F called "ID" which creates a "unique identifier" by stringing together the employee number, date, and AptID. In cell F2 type the following: =A2&B2&E2. You can copy that down to all the other rows. Next add a column G called "UniqueApt" which shows the Minutes again, but only if that appointment is "unique" (i.e., not the same employee and date and AptID). In cell G2 type: =IF(COUNTIF(F$1:F1,F2)=0,D2,""). Copy that down to all the other rows. Now your sumproduct would reference the new column G which shows the minutes only when the appt is unique: =SUMPRODUCT(--(A2:A100=382),--(B2:B100=4/1/2006),(G2:G100)) Hope that helps. Write back if stuck. "J" wrote: From a database of customer training appointments, I need to count the time each employee works each day. One customer is scheduled with an employee at a time. The current formula double-counts overlapping time periods. Current formula: =(SUMPRODUCT((Emp=382)*(Date=4/1/2006)*(Duration)))/60 The data looks like: Emp Date Start time Min. AptID 382 4/1/06 9:00:00 AM 60 123 382 4/1/06 9:00:00 AM 60 123 382 4/106 1:00:00 PM 90 223 382 4/1/06 2:00:00 PM 90 223 382 4/1/06 4:00:00 PM 60 333 382 4/1/06 4:00:00 PM 60 333 210 Total Minutes What formula would keep from double counting the same time period by ignoring subsequent records with the same AptID? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count duplicates once | Excel Discussion (Misc queries) | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Count excluding Duplicates | Excel Worksheet Functions | |||
Count Duplicates | Excel Discussion (Misc queries) | |||
count a group of numbers but do not count duplicates | Excel Worksheet Functions |