Following up on my previous sheet, here's an updated version.

Next challenge is to find top ranking sales reps using formulas only. There is not a fixed list of sales reps, and in the real data there are several hundreds. So looking at the attached version, I need formulas in O3 and down. This would normally not be that hard if only there was a list of sales reps, but as this varies from day to day and over a large number of departments (in the real data there are also more criterias), the formula will have to identify each sales rep from the C column, sum profits for each sales rep based on multiple criterias (for now just consumer sales, but will need to support more), and then use a MAX to get the top, second and third rep.

As a nice add-on, it would be cool to also be able to get the bottom three ranked.

Is this even possible? For a number of reasons I'm unable to use pivot tables or filtering / sorting, due to the import/export procedures for this data. Basically the raw data is swapped every day, and the results in calculations is used elsewhere. Number of rows, sales reps and products will vary.

Thanks!

sumifsarray2.xlsx