I have been asked to design a classroom booking form in Excel. Its very simple the layout is as follows:

mon tue wed thu fri etc.............

room1 training-------->
room2
room3
etc....

availability 2 2 2 3 3

It's the availability that I am struggling with once a room has been booked for the number of days this will reduce the availability count by 1 for the week. So the availability is the total amount of rooms avalable for any one day.

The problem I have is counting the availability especially as the bookings are spread across the dates using the merge and center button.

I need a clever way to count the availability in all the columns that the booking is made across, although the cell at mon:room1 has the booking in it then merged and centered across X amount of columns.

I hope you have understood what I am trying to do and can help me.

I really need help fast on this as I need the solution for 10am UK time tomorrow!!!!!!!!!!!! (only found out I needed to do it today, lol!!)

Thanks in advance, any examples would be fantastic.