Trying to count the number or customers of type x for subsequent time intervals

[Meggie]

Hello

I am trying again, this time to be clearer. I have time that is by the hour and I have arrivals and departures of type x customers. I would like to take account of each type of customer I have in the system during the subsequent time intervals. Please see below

These are an example of the time intervals, where I want to count how many customers in the system for the hour period. If they were in the system within the hour (arrive or depart, or int he entire time) I want to include them

Current dat
12/31/2000 22:0
12/31/2000 23:0
1/1/2001 0:0
1/1/2001 1:0
1/1/2001 2:0
1/1/2001 3:0
1/1/2001 4:0
1/1/2001 5:0
1/1/2001 6:0
1/1/2001 7:0
1/1/2001 8:0
1/1/2001 9:0
1/1/2001 10:0
1/1/2001 11:0
1/1/2001 12:0
1/1/2001 13:0
1/1/2001 14:0
1/1/2001 15:0
1/1/2001 16:0
1/1/2001 17:0
1/1/2001 18:0

Here is the arrival time and departure time of the customers. the Location is the type of customer. I would like to have columns set up next to current date, with the various location subheadings and how many customers are in the system at this tim

ARRIVAL DEPARTURE Locatio
12/31/00 21:22 1/1/01 1:20 D.CEMER
12/31/00 21:37 1/1/01 0:30 D.CEMPE
12/31/00 22:00 1/1/01 0:30 D.CEMER
12/31/00 22:13 1/1/01 0:05 D.CEMPE
12/31/00 22:12 1/1/01 0:20 D.CEMPE
12/31/00 22:15 1/1/01 0:10 D.CEMER
12/31/00 23:16 1/1/01 0:30 D.CEMPE
12/31/00 23:34 1/1/01 3:10 D.CEMERC
12/31/00 23:36 1/1/01 1:40 D.CEMER
12/31/00 23:49 1/1/01 0:37 D.CEMERC
1/1/01 0:07 1/1/01 2:27 D.CEMERC
1/1/01 0:12 1/1/01 1:00 D.CEMPE
1/1/01 0:13 1/1/01 0:55 D.CEMPE
1/1/01 0:38 1/1/01 2:10 D.CEMERC
1/1/01 0:42 1/1/01 3:10 D.CEMERC
1/1/01 0:51 1/1/01 1:52 D.CEMERC
1/1/01 0:51 1/1/01 3:10 D.CEMPE
1/1/01 0:58 1/1/01 2:05 D.CEMERC
1/1/01 1:32 1/1/01 2:50 D.CEMPE
1/1/01 1:39 1/1/01 3:30 D.CEMERC
1/1/01 1:55 1/1/01 6:45 D.CEMERC
1/1/01 2:22 1/1/01 3:35 D.CEMERC

Thanks a millio
Meggie

 

[Earl Kiosterud]

Meggie,

If the first list is in A2-down, and the second is in sheet Cust, with the
arrivals in A2:A23, and departure times in B2:B23, then this formula is for
the first time interval ( starting at 12/31/2000 22:00). I put it in B2,
but it can go anywhere.

=SUMPRODUCT((Cust!$A$2:$A$23<A3)*(Cust!$B$2:$B$23>A2))

It's a array formula. Use Ctrl-Shift-Enter. You'll need an extra entry at
the end of the first list for end-of-period time, because the formula looks
at the next entry for that time.

The following is for one customer type. The customer types are in C2:C23

=SUMPRODUCT((Cust!$A$2:$A$23<A3)*(Cust!$B$2:$B$23>A2)*(Cust!C2:C23="D.CEMERG
"))

Also array formula.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hello,

I am trying again, this time to be clearer. I have time that is by the

hour and I have arrivals and departures of type x customers. I would like to
take account of each type of customer I have in the system during the
subsequent time intervals. Please see below.

These are an example of the time intervals, where I want to count how many

customers in the system for the hour period. If they were in the system
within the hour (arrive or depart, or int he entire time) I want to include
them.

Current date
12/31/2000 22:00
12/31/2000 23:00
1/1/2001 0:00
1/1/2001 1:00
1/1/2001 2:00
1/1/2001 3:00
1/1/2001 4:00
1/1/2001 5:00
1/1/2001 6:00
1/1/2001 7:00
1/1/2001 8:00
1/1/2001 9:00
1/1/2001 10:00
1/1/2001 11:00
1/1/2001 12:00
1/1/2001 13:00
1/1/2001 14:00
1/1/2001 15:00
1/1/2001 16:00
1/1/2001 17:00
1/1/2001 18:00

Here is the arrival time and departure time of the customers. the

Location is the type of customer. I would like to have columns set up next
to current date, with the various location subheadings and how many
customers are in the system at this time