Find unique entries and create ranked list with formulas only

[opheim]

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

[daffodil11]

Totally possible. I'll see if I can whip you up some autopopulation.

[daffodil11]

This project made for a good lunch.

The ranking tab is accommodating up to 100 unique entries, and 9999 rows on the first tab. Copy A100:G100 down further to accommodate as many uniques as you think you'll have.




matchindex ftw.xlsx

[opheim]

Amazing Thank you so much! I had forgotten some rather weird variables, but managed to work around them so your solution still works. It makes Excel pretty slow though, due to the amount of data, might have to reconsider the ambitions here.

Thanks again!