Check room availability for same date.

Francis PJ P. · 04/24/23

Hello Smartfamily !

I have a sheet where I need to check if a room is available for the same date.

The screenshots below shows how the column "Availability" SHOULD behave.

-----

For the date April 24 2023, I select Room 1. Room 1 is not already selected for that date so "Availability"@row shows a checkmark

As soon as I select Room 1 again for the same date, "Availability" shows a X so I can easily see that it's already been selected.

-----

What is the formula I should have in the "Availability" column?

Thank you ! Keep being Smart !

ShelbyWarren · 04/24/23

@Francis PJ P. Is this data all kept in one sheet or will you be referencing another sheet that contains the room numbers?

Samuel Mueller · 04/24/23

@Francis PJ P. try something like this

=if(countifs(room:room, room@row, date:date, date@row)>1, "No", "Yes")

Francis PJ P. · 04/24/23

All in one sheet. :)

Francis PJ P. · 04/24/23

This works ! Thank you!

I have "upgraded" your suggestion a bit so that if no room has been selected, the Availability row does not show a green checkmark.

=IF(Room@row = ""; ""; IF(COUNTIFS(Room:Room; Room@row; Date:Date; Date@row) > 1; "No"; "Yes"))

Answers