number in system using arrivals and departures

[Guest]

Is there and easy way to figure out the total number of persons in a system if have access to the arrival and departure times. I have tried to use counts with if statements, but I keep getting the number 1. I know this is not correct. Is there any special macros or functions

Thanks

 

[Frank Kabel]

Hi
you may provide some more details about your data. Could you post some
example rows (plain text - no attachments please) and describe your
expecte result

 

[Bob Phillips]

Would you not just count the departures and subtract that from a count of
the arrivals.

For instance

=COUNTA(B:B)-COUNTA(A:A)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there and easy way to figure out the total number of persons in a

system if have access to the arrival and departure times. I have tried to
use counts with if statements, but I keep getting the number 1. I know this
is not correct. Is there any special macros or functions.

 

[Guest]

Sample of the data in questio

ARRIVAL DEPARTUR
12/31/00 21:22 1/1/01 1:2
12/31/00 21:37 1/1/01 0:3
12/31/00 22:00 1/1/01 0:3
12/31/00 22:13 1/1/01 0:0
12/31/00 22:12 1/1/01 0:2
12/31/00 22:15 1/1/01 0:1
12/31/00 23:16 1/1/01 0:3
12/31/00 23:34 1/1/01 3:1
12/31/00 23:36 1/1/01 1:4
12/31/00 23:49 1/1/01 0:3
1/1/01 0:07 1/1/01 2:2
1/1/01 0:12 1/1/01 1:0
1/1/01 0:13 1/1/01 0:5
1/1/01 0:38 1/1/01 2:1
1/1/01 0:42 1/1/01 3:1
1/1/01 0:51 1/1/01 1:5
1/1/01 0:51 1/1/01 3:1

 

[Frank Kabel]

Hi
if these are date/time fileds and you have your start arrival date/time
in cell C1 and the latest departure time in D1 (that is you want to
count all perople who have arrived after C1 and have not departed
before D1) try
=SUMPRODUCT(--(A1:A100>=C1),--(B1:B100>D1))