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?