Help with Pivot Table to show capacity, sold, load and remaining

Hi,

I've been trying to come up with a Pivot Table to calculate load and remaining, but I am stuck.

I have the # of rooms to sell:
Smallest: 4
Medium: 5
Biggest: 6

I have the # of rooms booked (please see attached)

I am currently calculating the load and the remaining rooms separately, outside the Pivot Table.

Would it be possible to combine all these in a Pivot Table so I wouldn't have to manually set up calculations?

Thank you so much for any assistance or suggestion.
Fern

Hi Rory!
Yes, I just noticed that I have a Power Pivot tab, but I've never used it. Do you have any suggestions for me?
Thank you!
Fern

Hi again.
Thank you so much but this won't work because we can't count all rooms together.
Some rooms are booked on different dates, for example room #104 is Smallest and it's booked on Apr 26 and May 20. So we need to count both dates under Booked (which is perfect), but the Total Rooms (rooms we have to sell) is adding up types.
This is my conundrum....
The rooms to sell should be a fixed number. 4 smallest, 5 medium, 6 biggest on every single date.
I tried doing a vlookup for the fixed number of rooms, but the numbers keep adding up for every booked date.
Does this help?
Thank you.
Fern

Yes. The rooms to sell is fixed on every date and the room numbers listed ("rooms that actually exist")on the Data tab are the rooms that are actually booked on those dates.

Hi Rory!

Wow! This is amazing, it's exactly what I am looking for!

I didn't know that you could have more than on table in the Pivot Table....
And I don't know how you calculated Booking % and Available Rooms, but I guess you might have used Power Pivot.

I will have to study your attachment and learn more about Power Pivot and Pivot Tables.

Thank you very much, I really appreciate your time and help! You rock!!!

Have a great day!
Fern