How many people were in my room?

[jack6602]

Hello.

I want to be able to see how long people was in my room.
To do this, I have a excel file with the hour they came in and the hour they left.
But I am having a hard time finding a way to see how many people were in the room for a CERTAIN hour.

Example:

(They came) (They left)
1 3
1 2
2 2
3 5
4 5

So, hour 1: There were a total of 2 people in my room.
Hour 2: There were a total of 3 people.
BUT.. Hour 3: Since two people left at hour 2, then there is a total of 2 people in my room at hour 3.

I assume I have to use a combination of count and if but I have no idea how to do it or where to start.
Help would be thankful.

I am practicing excel and trying out different things

[daddylonglegs]

Assuming "in" hours are in A2:A10 and "out" hours in B2:B10

If you have a specific hour in D2 use this formula in any version of Excel

=SUMPRODUCT((D2>=A$2:A$10)*(D2<=B$2:B$10))

or in Excel 2007 or later....

=COUNTIFS(A$2:A$10,"<="&D2,B$2:B$10,">="&D2)

[jack6602]

Thanks a lot it worked fine

[jack6602]

Can I ask something?

Is there any way to double check that the numbers given are correct? I can not do this manually because it's a lot of numbers. I want to see if it works as good when the day changes.

[daddylonglegs]

What do you mean, "when the day changes"? What will the data look like in that case, will you have 23 in the "in" column and 5 in the "out" or something similar?

[jack6602]

Exactly. I am worried that it might break when it comes to day change. (I have a new parameter for day though and it seems to work but I don't know a way to manually control it unless I have to go through hundreds of lines manually)

[jack6602]

I am sorry but can I ask one more thing; What does that function do exactly? I want to learn more
I can not understand it fully.

[jack6602]

I figured it out

[shg]

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You should also look in Help and explain what you don't understand.