Finding top 5 results and showing top sub result under those top 5

Mandeep

Hi I work for a sales company's and would like to add the top five agents names to my dashboard, under each agent i would like to have their most sold product.

Is there an elegant way of finding these agents and their most sold product from the grid that i update every morning? Ideally i would like to avoid creating a column for each product, doing something like a countifs and the using the RankEQ function. That is how i can imagine it can be done but we have over 70 products the agents can sell so I want to find a cleaner solution, is there something like a MaxIf solution prehaps?

I've done something similar for states sold to, where i used the max function and then a vlookup to find the state with the most units sold to but i need to scale this so I can show the top agents 1-5.

ericncarr

If I'm understanding right, I'd do a little metadata lookup table for this and then have it as a metric to show on the dashboard.

To get the total number of sales, I'd create a little table of all the agents and calculate their total sales:

=SUMIF(Agent:Agent, [Agent Total Sales]@row, [# of sales]:[# of sales])

To get the Top Sales Agent, get the Agent with the largest n number of sales, so the first row is 1 (for the largest), the second row 2 for the second largest, and so on:

=INDEX([Agent Total Sales]:[Agent Total Sales], MATCH(LARGE([Total Sales]:[Total Sales], [Top Sales Number]@row), [Total Sales]:[Total Sales], 0))

To get the Most Sold Product by that Agent, I'd then Collect the Product based on the agent and collecting the highest value amongst products they'd sold in order to get the associated product, so a nested collect:

=INDEX(COLLECT(Product:Product, Agent:Agent, [Top Sales Agent]@row, [# of sales]:[# of sales], MAX(COLLECT([# of sales]:[# of sales], Agent:Agent, [Top Sales Agent]@row))), 1)