Ranking based on multiple criteria

Hi km and welcome to the forum,

I think I've accomplished what I think you've asked for. On the other hand, I'm not sure it is what you really want. See the attached Pivot Table where it has 3 groupings, sorted and a ranking column per your question above. I'd call this question "be careful what you ask for". Do you want to revise your question?

PT Ranking with 3 groups.xlsx

I just found a SUMPRODUCT formula that groups by sales rep and then ranks by department code, but can I add other criteria onto the formula?

The formula is

Formula:

Please Login or Register  to view this content.

Hi km,

Can you add your formula to your workbook and attach it again. Then show what the final answer will be, in a few cases. I'm still not understanding the question. I do believe SumProduct will take more than 2 criteria. BUT - it needs the same number of rows to work with when it does the SumProduct.

Looking at the "Desired Ranking" it appears that you want to group (sort) as well as rank. If that is the case then it would seem that, with the aide of a helper column (which may be moved and/or hidden for aesthetic purposes), this could be accomplished using a sort.
The helper column, header "Modified Close Date (target)", is populated using the IF function i.e.: =IF(C2<>"",C2,0)
The formula for the "Rank" column is: =IF(A2<>A1,1,SUM(F1,1))
1) Select a cell in the data range,
2) From the home tab select Sort&Filter > Custom Sort,
3) Level 1: Sales Rep A to Z,
4) Level 2: Dept. Entry smallest to largest,
5) Level 3: Modified Close Date (target) oldest to newest,
6) Level 4: Sales Amount largest to smallest
7) Select OK
Note: to test I removed the date (5/20/2016) from one of the entries assigned to KC in Dept 7000 (highlighted)
If you only want to see the top 7, as indicated in the "Desired Ranking" tab, apply a filter to the "Rank" column and leave only 1 through 7 selected.
Let us know if you have any questions.