This is for calculating recruiting for my real estate agents. I'll give a general overview of what my goal is for this spreadsheet, and then where my challenge is. If you have better ways of structuring it, I'd love to hear your ideas, but it may be a bit of overkill for the actual question. Hopefully not.

If they recruit, they earn a piece of the recruited agent's commission, which comes off the company portion of those earnings. So, Agent 1 recruits Agent 2, then Agent 1 makes a piece of Agent 2's commission from the company portion. If Agent 2 recruits Agent 3, then Agent 2, and Agent 1 will make money on Agent 3's closing. This will go down to Agent 5, with each agent connected to the previous recruit earning a piece. I call it the Revenue Share Plan:)

It starts with an Agent. They recruit directly to their Frontline. The Frontline agent's recruits are the Downline 1. The Downline 1 recruits are the Downline 2. The Downline 2 recruits are the Downline 3. So Agent, Frontline, Downline 1, Downline 2, Downline 3.

I need to track every agent associated with the original agent in Agent Roster, which is all company agents. So, if I had a dropdown of an Agent that was selected, it would show the list of all Frontline and Downline agents associated. It needs to be able to handle thousands of agents if Agent Roster were to be thousands of agents long.

I've been working on it for a while and have been able to get the Frontline formula to work. You’ll see it in yellow as Lynn, Chad on the Agent Roster sheet. When I move over to Downline 1, and try the same calc using cell H3, and working from the Data Set in F it freaks out because I’m now asking it to find all occurrences of Tonya, Cozett, Chris, & Bart, rather than the one cell like in the previous calc. So, the Downlines are posing a problem since they have multiple criteria.

I need it to be as automated as possible to keep any errors at bay.

Also, to open up certain Downlines, an Agent needs a certain amount of Frontline recruits. For instance, 5 Frontline to unlock Downline 1, and 10 Frontline to unlock Downline 2, and 15 to unlock Downline 3. Then I'm going to add a calc that will automatically show the payouts for each agent listed based on what % payouts are associated with each tier.

So ideally, a dropdown to choose an Agent, and then only key in the GCI (Gross Commission Income), it calculates based on which tier and shows all the payouts for that transaction to whoever is affiliated with the Agent closing the sale.

Below are a few screenshots. The one showing code for the Frontline works fine because it's only pulling from Chad Lynn. In the second screenshot, it needs to pull from Tonya, Cozett, Chris, & Bart, and search them in the Recruited By column and return the Agent from Agent Roster effectively showing who they recruited for Frontline 1's results. Then repeat the process in Frontline 2 & 3. In the calc in H3, this is the section that’s giving me a fit, because F2 is a single cell, and I can’t get it to allow any additional criteria like an “and” or F2:F6, and I’m wondering if there needs to be perhaps another formula after =F2 to solve the issue, or if Aggregate altogether may be wrong, but that would mess up the entire calculation, and this is the only one I know of that will show the list of results without matching it cell for a cell like a match would do. Ok, who has the skills to dominate this question!?!

Possible issue with this code...

AGGREGATE(15,3,($D$3:$D$10=$F$2)/($D$3:$D$10=$F$2)

Mark nailed it...here are some shots of the spreadsheet now.