Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
j j is offline
external usenet poster
 
Posts: 128
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 158
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
j j is offline
external usenet poster
 
Posts: 128
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 158
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count duplicates once Tinkerbell1178 Excel Discussion (Misc queries) 2 December 1st 10 02:42 PM
Count Employee Work Time - Don't Double-count Overlapping Apts. J Excel Worksheet Functions 0 April 27th 07 05:52 AM
Count excluding Duplicates GRM via OfficeKB.com Excel Worksheet Functions 12 November 15th 05 10:07 PM
Count Duplicates Jonathan Excel Discussion (Misc queries) 2 April 8th 05 03:23 PM
count a group of numbers but do not count duplicates Lisaml Excel Worksheet Functions 2 January 27th 05 12:19 AM


All times are GMT +1. The time now is 12:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"