Proper Counting of Days

Actually I see from your original formula that if you have a stay from 27th June to 3rd July that counts 3 days in July and 3 in June (effectively not counting the start date), whereas my version is counting 4 in June and 2 in July (not counting the end date) so if you want that to still apply then you can amend the AA9 formula (copied down) to this

=SUM(IF(F$2:F$34<X9,0,IF(E$2:E$34>EOMONTH(X9,0),0,IF(F$2:F$34=E$2:E$34,1,IF(F$2:F$34>EOMONTH(X9,0),EOMONTH(X9,0),F$2:F$34)-IF(E$2:E$34<X9,X9-1,E$2:E$34)))))

confirmed with CTRL+SHIFT+ENTER

or if you want to keep the helper columns just use this formula in J2 copied across and down

=IF($F2<=J$1-DAY(J$1),0,IF($E2>J$1,0,IF($E2=$F2,1,MIN(J$1,$F2)-MAX($E2,J$1-DAY(J$1)))))