Grouping By Criteria Across Multiple Columns

I think you’d probably benefit from abstracting out parts of your current table into their own tables.

I can imagine you setting it up like this:
Table 1 - “People”
Table 2 - “Locations”
Table 3 - “People-Location Records”

People has a record for each person with all of the relevant info you need for each person. Also included will be a linked record field, linking to the People-Location Records table, allowing multiple linked records.

Locations has a record for each office you have, with any relevant info you want to record about each office. Also included will be a linked record field, linking to the People-Location Records table, allowing multiple linked records.

People-Location Records has a record for every time a Person moves to/joins a new Location. You can have a field for the “Date” the Person it links to joined (or will join) the Location it links to. Also, you can add a checkbox field called “Archive” to mark a record as no longer current.

Next, you can use FILTERS in the People-Location Records table to create views showing only current records (show only records where “Archive” is unchecked AND where “Date” is on or before TODAY). You can then GROUP those records by Location to get a count of how many people are at each location currently in the summary bar.

This also allows you to create “projected” location changes for a person by creating a Person-Location Record with a date set in the future. I would add another checkbox you can use for creating a “Projected Head-Count” view – call it “Will Move Soon”, and check it on a person’s current Person-Location Record when you create a future, “projected” location change for them. If you use filters cleverly, you can create a view now that shows current records where “Will Move Soon” is not checked, AND show records where the “Date” is after TODAY.
Sometimes the filters can be a little difficult for doing complex things like that because you can only use “AND” conditions or “OR” conditions, but not both – you can’t mix them. In cases where you run into that limitation, the best option is to create a Formula field in your table that does uses conditional statements to do the filtering for you and output some other value that you can filter on.

I realized half-way through writing this that, if you are new to Airtable, this may be a bit overwhelming. I’m heading out for my lunch break now, but if you can hold on a bit, when I get back I may be able to throw together an example base for you that you can copy and use or just mimic in your own base.

EDIT
Also, take a look at the general advice I gave in this recent post:

Her situation is a little bit similar to yours in the need to expand out to multiple tables and create entity relationships between the tables.

Here you go, Emma. This is a simple mockup base demonstrating what I described above. Once you join as a collaborator, feel free to copy the base to your own workspace if you want to dig in further.

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

Notice the two Views I made in the “People-Location Records” table - “CURRENT” and “PROJECTED”.
I used a field called “Status” to determine the status of a record as either:

• “Old”
• “Current”
• “Moving Soon”
• or “Future”

The CURRENT view filters by showing only records with status “Current” or “Moving Soon” (“Moving Soon” means the record is current, but there are plans for the person to move in the future).

The PROJECTED view filters by showing only records with status “Current” or “Future”.

You’ll notice that “Jim T. Jones” has an “Old” record, marked as Archive = TRUE() (checkbox) - he used to be at “HQ1”, but this record shows up in neither of the views above, because it is “Old”. But he moved on 8/9/2018 to “HQ2”, so that is his current location, and it shows up in the CURRENT view – however, there are plans for him to go back to “HQ1” on 1/8/2018, so I marked his current location record as Will Move = TRUE(), and created a record for his projected location change. The PROJECTED view reflects that in the future, he will be back at “HQ1”, and that view is sorted to show the most future dated records at the top, so those “Future” records are always at the top of the list for a location.

Also, you can see in the Summary bar for each Location a “Count” of how many records there are under that group (each one representing a person at that location).

I believe this should get you at least most of the way towards what you are wanting. You may want to see if you can get more granular with the “Record Status” to display “Will Move < 1 Mo”, “Will Move 1-2 Mo”, “Will Move 2-3 Mo”, etc so that you can make Projection views to show more granular periods of time, where the one I created currently encompasses ALL future moves in one view.

This is so helpful! Thanks @Jeremy_Oglesby I’ll mess around and see what I can achieve