COUNTIFS on number of room nights

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.

Answers

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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.