calculate per day usage with a number range

  • Thread starter Thread starter peabody
  • Start date Start date
P

peabody

I have the check in date and the check out date for a large group. I would
like to see how many rooms are used per day for the period.
Column A = Check in date (ex: 2-feb)
Column B = Check out date (ex: 10-feb)
How can I tell how many rooms were used on 6-feb, how many on 7-feb, etc?

Thanks so much for your help!
 
Put the date of interest (e.g. 6-feb-2008) in D2 then in E2 use this formula

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

You can copy the formula down to calculate for more date in column D
 
I tried this and it does not work. Close, but not quite. As you can see
from below, it returns 4 for 2/4/07 when the correct number of rooms for that
night is 2.
A B D E
2/1/2007 2/4/2007
2/1/2007 2/5/2007 2/4/2007 4
2/3/2007 2/5/2007
2/2/2007 2/4/2007



1-Feb 2-Feb 3-Feb 4-Feb 5-Feb
1 1 1
1 1 1 1
1 1
1 1
 
Back
Top