Excel formula with date ranges

You use a countif statement like this:
=IF(COUNTIF(B1:H500,A1),"X","")
which says that if there is a count of at least one of the value found in A1 in the range then do the rest!

Nope! didn't make sense at all, why not post an example workbook so we can see what you are trying to achieve!

Here is an example. I tried to attach an example sheet, but it didnt seem to work.

Please Login or Register  to view this content.

[/QUOTE]

Anyway, say the cell with the Z is the one with the formula.

Basically the formula should result in an X if:
a) the date on the left of the cell (column A) is equal to any of the dates in the date list (column F) AND
b) the room in the column of cell Z is equal to the room in Room List that is in the same row as the date that was derived in part A

so, for instance, the cell would result in an X if, and only if there is a date in the Date list = 01/05/08 (TRUE) and the room adjacent to it in the room list is Room 1 (TRUE).

if the applicable cell was cell Y, then the cell would result in an X if, and only if, there is a date in the date list = 03/05/08 (TRUE) and the room adjacent to it in the room list is Room 2 (FALSE as it is room 3). So in this case it would remain blank.

So what would the formula be in excel for the applicable cell?

Hope this is clearer now!

Thanks

Dan