Counting Occurrences During Date Range

RBro79

I have a roster of temporary workers that come in on different days and leave on different days. I need to calculate expected hotel nights by counting the number of workers who will be in the city on each day. I'm at a loss on how to do that. I really just need to know how many hotel nights to expect to need each month (1 room/person/night).

Paul Newcome

Are you able to provide screenshots of your source data for context (sample data will work).

RBro79

Here's an example of the report I'm using. What I'm doing manually is counting how many folks are here on each day. What I want is to spit out how many people are here each day for a month (5 EEs x 10 days = 50 hotel nights, for example). I don't know if I can do this with a sheet summary or some other way. We have an enterprise account with pivot table functionality, but I haven't played with that yet.

Paul Newcome

I would suggest a separate sheet where you list every date you want to display down a single date type column. Then you would use a COUNTIFS with cross sheet references that looks something along the lines of:

=COUNTIFS({Start Date}, @cell<= [Date Column]@row, {End Date}, @cell>= [Date Column]@row)