How to total vaules over a date range...

[Jonbones22]

Hi!

I am trying to figure out how I can total a tally of counts that fall over a date range. I'm sure there is a way but I am not that educated in excel to execute.

Basically, my data is hotel reservation room nights:

ARRIVAL_DATE	DEPARTURE_DATE	NIGHTS
13-Apr-14	20-Apr-14	7
13-Apr-14	15-Apr-14	2

I need to the number of room nights over a 90 day period for 1000s of reservations filled in like so:

Date	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr
Total Number of Room Nights	2	2	1	1	1	1	1	0

I sure there is a fairly simple way to accomplish, but I can't figure it out. Anyone have any ideas?


Thanks!

[popipipo]

Without a xls sample I should say try one of these:
sumif
sumifs
sumproduct
pivot table

[newdoverman]

You are probably looking at something like this:

[Jonbones22]

This will actually help in a different project I have, thanks! I need to know how many rooms nights total each date in the time period. Example would be when I have two reservations A: 4/13 - 4/15 (2 nights) and B: 4/13 -4/20 (7 nights) the total for each date would be 4/13 -2 rn (room night), 4/14 - 2 rn, 4/15 - 1 rn, 4/16 - 1rn, 4/17 -1 rn, 4/18 - 1 rn, 4/19 - 1 rn, 4/20 - 0 rn.

Since the departure date does not include a room night, I'm guessing an adjustment of -1 needs to be added. Or remove departure date from the equation i guess...


I've updated the original post with better description of data and layout.



Thanks!

[Jonbones22]

Book3.xlsx

Here is exampe of data.

[popipipo]

Maybe this?

[bebo021999]

Assuming your calendar dates start from G1 and accross
Type in G2 and drag accross:
=SUMPRODUCT(($A$2:$A$22<=G$1)*($B$2:$B$22>G$1))

[newdoverman]

Countifs also works with your chart. For easy comparison, I put your chart in the same location as bebo021999.

Please Login or Register  to view this content.