COUNTIFS on number of room nights

Estela Galicia

Hi there I am trying to use COUNTIFS to count the number of room nights per night, based on hotel check-in and check-out date column, referencing to another sheet.

=COUNTIFS({AH 2023 Speaker Travel Tracker Range 1}, "<="@cell(2023, 01, 28),{AH 2023 Speaker Travel Tracker Range 2},">"@cell(2023, 01, 28))

The formula returns with #UNPARSEABLE error.

Based on sheet with columns of check-in date and check-out date, I would like the countifs formula to return the counts of numnber of rooms per night that is required per night.

28/01/2023 - x number of rooms

29/01/2023 - x number of rooms

and so on..

Apprecaite the help.

Andrée Starå

Hi @Estela Galicia

I hope you're well and safe!

Try something like this.

=COUNTIFS({AH 2023 Speaker Travel Tracker Range 1}, <=@cell(2023, 01, 28),{AH 2023 Speaker Travel Tracker Range 2},>@cell(2023, 01, 28)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Estela Galicia

Hi Andree

The below formula returns with #UNPAREABLE ERROR

=COUNTIFS({AH 2023 Speaker Travel Tracker Range 1},<=@cell(2023,01,28),{AH 2023 Speaker Travel Tracker Range 2},>@cell(2023,01,28)

The column which the formula is referencing to is on date format, so not exactly YYYY, MM, DD as the formula. Is this maybe the reason why tehre is an error? I am attahing the image here.

Thank you.

Screenshot 2022-11-03 124652.jpg