Count the adjacent cells date for people database on their check-in check-out dates

[malcolmaaron22]

I would like to know if there's a way to count if a range of dates is within the given date. In this example, the occupied rooms in a fixed single location based on their check-in and check-out dates, Person A check in on 15/08 and check out on 19/08, I would like the formula to count 1 on summary table on 15/08, 16/08, 17/08 and so on.

There's a lot of examples to check if a date is between a range of dates but can't find the other way around so far.

Here's the example on my questions along with the example file attached:

1. Table 1 is the database of people, real case scenario I have around 3000+ names to work with
2. Table 2 is the summary count of each date I want it to be. Example below should clarify more on this
3. Table 3, instead of doing it manually and count, would be useful if there's any formulae/VBA to simplify this.

• Device and OS platform: PC/Windows 10 Enterprise Version 1909
• Excel product name and version number Microsoft 365 Apps for enterprise; Version 2102

[#2]

@malcolmaaron22 

Formula for the Count Pax column:

=COUNTIFS(Table2[Check In Date],"<="&[@Date],Table2[Check out Date],">="&[@Date])

I don't understand how you arrived at the numbers in your sample results. On 20-Aug, for example, Person A had already checked out, so only B and C remained, and D checked  in, hence I count 3, but you have 4...

I would even argue that you should use

=COUNTIFS(Table2[Check In Date],"<="&[@Date],Table2[Check out Date],">"&[@Date])

if you don't want to count someone on the day they check out.