Calculating number of people

D

Denise

I have a very large spreadsheet for each month. Each one has approximately
2500 - 3000 entries. I have arrival times and discharge times for each
entry. I need a formula that will tell me how many people were here at
12:00, 1:00, 2:00, 3:00 .... all throughout the day.
 
A

Art

Denise,

An example:

A1:A4 has people names
B1:B4 has arrival times
C1:C4 has departure times
A7 has the time you're interested in

B7 has the following formula:
=SUM(IF(A7>=$B$1:$B$4,IF(A7<=$C$1:$C$4,1,0),0))

IMPORTANT: this is an array formula. After you type it in, you must hit
ctrl-shift-enter, for it to work. If you do that you will see "curly
brackets - {}" around your formula. Do not enter the curly brackets yourself.

Art
 
P

pinmaster

Try this:
=SUMPRODUCT(($B$2:$B$5<=E5)*($C$2:$C$5>=E5))
copied down
$B$2:$B$5 arrival time
$C$2:$C$5 discharge time
E5 time to lookkup

HTH
JG
 
Top