Calculating number of people
 

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.

Calculating number of people
 

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

"Denise" wrote:

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.

Calculating number of people
 

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

"Art" wrote:

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

"Denise" wrote:

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.