Sum Two Criteria then Rank

[LionAround]

Hello all!

Please help me figure out the last piece of my puzzle.

I have a table with sales figures for Acct. Managers & Retailers. I would like to rank the total sales for each Acct. Manager/Retailer combination, so that each Acct. Manager's Retailers are sequentially ranked starting at 1 for each Acct. Manager.

So far, I have successfully ranked the total sales for each Retailer for each Acct. Manager. What I haven't figured out is how to force the rank to reset to 1 for each Acct. Manager, and to ignore the duplicates while assigning the rank. I would like the rankings to be sequential (1,2,3 vs. 1,6,9, etc.).

A sample is attached. I welcome any and all suggestions!

-Diane

[dilipandey]

Hi and welcome to the forum.

Try looking at below post of our forum :-

http://www.excelforum.com/excel-gene...-in-excel.html

Regards,
DILIPandey

<click on below 'star' if this helps>

[LionAround]

Hi DILIPandey,

Thanks for the suggestion! Unfortunately it isn't producing the results I need. For instance, George/Costco should be ranked 1 but the formula below returns 23:

Please Login or Register  to view this content.

I've got the ranking part working (Col. F of my attachment) using SUMPRODUCT on a concatenated column. What I can't quite figure out is how to make those rankings sequential, i.e. ignore duplicates, and to start over at 1 for each Acct. Manager.

Thank you so much for your help!

-Diane

[dilipandey]

Hi Diane,

Not sure why it's not working in your workook.. see the attached workbook where it is working perfectly..

Rank on multiple criteria using SumProduct.xls

Regards,
DILIPandey

<click on below 'star' if this helps>

[Marcol]

Not sure as to what your final objective is.

See if this helps, Column F might not be needed.

[LionAround]

dilipandey, thanks! I see that it is working in your attachment, still not quite exactly what I was looking for - in that example I would want to sum the scores for each student and then rank them within each class. Saving for reference though, as I will probably run into a similar scenario eventually.

Marcol, Column F is exactly what I needed! Your revision to my Column D formula was also helpful - that will come in handy in a couple of other places. One question - in the formula in F2, you reference F1:

Please Login or Register  to view this content.

Is that how you are getting the sequential rank, by referencing the cell above?

Thank you both for your help!

-Diane

[Marcol]

Is that how you are getting the sequential rank, by referencing the cell above?The key column is Column E.

No, it's just filling in the cells in Column F

If there is a value in say, E7, then calculate and show the result in F7.
If there is no value in E7, i.e. E7 = "" then, in F7, repeat the previous result in Column F, i.e. the value in F6

[LionAround]

That makes sense now, thanks so much for your help!