Works in Process Formula

[ARLarson]

I have a large data set with groups of people using a facility for different date ranges. I am trying to figure out how many groups are at the facility for each calendar day of the year. The start, end and all in-between dates should count as a group at the facility. The attached spreadsheet is a simplified version of my data set that shows the essential information. Any help with this WIP function will be much appreciated.

Thanks!
Andrew

[Pete_UK]

Do you want to be able to enter a date and get the result for that day, or to list all the days in a year and have the results for each day shown?

Pete

[Glenn Kennedy]

I assumed the latter. So, with a list of dats starting in G2, use this:

=COUNTIFS(C:C,"<="&G2,D:D,">="&G2)

[Glenn Kennedy]

Incidentally, April only has 30 days (D24)....

[ARLarson]

Awesome! I assume there is a simple modification to get the total number of people per date?

Thanks Glenn!

[Glenn Kennedy]

Yes. This will do total numbers of people:

=SUMIFS(B:B,C:C,"<="&G2,D:D,">="&G2)

[ARLarson]

Perfect. Thank you much!

[ARLarson]

The results of this data set analysis were higher than expected so I thought about it and shared it around. I think the discrepancy results from our need to know the number of groups and people using the facility per night, rather than per day. I expect that running an analysis of groups per night would result in smaller numbers than groups per day because one group's entry day and another's exit day would not be counted as an overlapping use in the per night analysis.

How would I would I calculate overlapping groups per night at the facility?

[bebo021999]

slight mod

=COUNTIFS(C:C,"<="&G2,D:D,">"&G2)

=SUMIFS(B:B,C:C,"<="&G2,D:D,">"&G2)

Does it work?

[ARLarson]

It seems to work! Thank you.