If Statements

Joseph Noel

Hello All,

I am attempting to create an If statement that raises a flag if two rows have the same location and the same date.

This submission sheet will be used when people visit a location and we do not want any double bookings. So I was planning on having a formula raise a flag and then have a report that looks for the flag to put on our dashboard.

The sheet is very simple, just a list of location names in one column and the date the person is planning on visiting that location, there are several hundred locations to choose from.

Appreciate the help or advice if you think I am going about it the wrong way.

Thanks

David Joyeuse

Hi @Joseph Noel

You should use either one of these formula in your flag column:

=IF(COUNTIF([Location]$1:[Location]@row, [Location]@row, [Date]$1:[Date]@row, [Date]@row)>1, 1, 0)

This will raise a flag if someone book a date and location already booked.

Other alternative is:

=IF(COUNTIF([Location]:[Location], [Location]@row, [Date]:[Date], [Date]@row)>1, 1, 0)

This will flag everytime a Location and Date is selected more than once.

Choose either depending on how you want the flag to be raised :)

Hope it helped!

David Joyeuse

Hi @Joseph Noel

You should use either one of these formula in your flag column:

=IF(COUNTIF([Location]$1:[Location]@row, [Location]@row, [Date]$1:[Date]@row, [Date]@row)>1, 1, 0)

This will raise a flag if someone book a date and location already booked.

Other alternative is:

=IF(COUNTIF([Location]:[Location], [Location]@row, [Date]:[Date], [Date]@row)>1, 1, 0)

This will flag everytime a Location and Date is selected more than once.

Choose either depending on how you want the flag to be raised :)

Hope it helped!

Joseph Noel

@David Joyeuse Thank you. I had googled it and got a similar formula that didnt work, thank you this worked perfect. Also thanks for the quick response.