COUNTIF Issues

I have butlers assigned to guests' rooms. I need to calculate the number of rooms they have arriving, departing and those that are still 'in house'

Each guest has a membership level. (yellow, green & red as per my example)

I have worked out the formulas for the arrivals and departures but I cannot work out the 'in house' ones. Please help.

In the attached file, cells A11:G20 are fully editable. This file will be worked upon daily with people with very little excel knowledge.

Cells A1:A6 have to stay the same. Cells C3:K6 are the results I am looking for. The red columns are what I cannot work out.

Cells J12:R15 are the desired results