I have a complicated sum that I am trying to acheive through a funtion.

Here is the problem.

I have a table which says the name of a project and the number of staff
working on that project and the main floor that the project has been assigned
to.

eg.
PROJECT FLOOR PEOPLE
bridge 1 20
road 2 1
aeroplane 1 12


I have another table that I use to assign projects to different rooms.
Floor, Room and Capacity are fixed and Project is variable.

eg.

FLOOR ROOM CAPACITY PROJECT
1 1 10 bridge
1 2 10 bridge
1 3 10 aeroplane
2 1 5 aeroplane
2 2 2 road

What I would like is a function that sums the amount of people on a floor.
This sounds easy but is complicated by "overspill". Overspill is illustrated
by aeroplane. Aeroplane has been assigned to floor 1 as it's main floor,
however it has too many people and will therefore have to occupy rooms on
another floor. I would like the sum to take this into account, by assessing
which is the shows main floor and taking any overage as the amount on the
next floor.

Therefore the amount of people on floor 2 equals 3, because there is one
person on Road and 2 people on Aeroplane (given that Aeroplane has been
assigned 10 seats on floor 1 and will then overspill into floor 2 for its
remaining to people)

I hope that makes sense. Help appreciated.