Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Change number format from text to number? | New Users to Excel | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
Is there a template for a number of people traveling by month? | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |