Count Employee Work Time - Don't Count Duplicates

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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!

 

[Guest]

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 . . .