Ranking on two integer columns

[Kmitchell]

Hi, I am struggling to locate a formula that would solution my expected outcome. Essentially I am looking to rank two columns both based on integers.

Column B represents the utilizers, I want this to be the first factor for the rank. Column E reflects the rate change. I want this to be the second factor for the rank.

The goal is to identify the top utilized drugs that also experienced an increase and a decrease in the rate change. If we simply look at the rate change only the rank of drug564 it's the steepest rate change of $2.07 but there are only 44 utilizers. I would like the #1 rank to be on Drug 11 that had 1,494 utilizers and a rate change of $0.01 because this drug is the most utilized with a rate increase despite it being a $0.01 change.

Perhaps there is a work around, helper columns?

Open to any and all suggestions.

 

[Cubist]

Adapt the range, try:

=SORTBY(A:E,B:B,-1,E:E,-1)

 

[Kmitchell]

Adapt the range, try:

=SORTBY(A:E,B:B,-1,E:E,-1)

Thank you for the quick reply. I attempted this and the formula returned #SPILL!. Thoughts?

 

[Cubist]

You need to leave enough cells for the formula to spill out. Clear out things below and to the right or pick somewhere you have a lot of space.

 

[Kmitchell]

You need to leave enough cells for the formula to spill out. Clear out things below and to the right or pick somewhere you have a lot of space.

Thanks, its not working for me for some reason, takes some time to process then it #Spill!, maybe not enough resources on the machine

 

[Cubist]

Read here on #SPILL!

How to correct a #SPILL! error - Microsoft Support

How to correct a #SPILL error in Excel (when a formula returns multiple results and cannot return them to the grid).

support.microsoft.com

Try it on a small range first and see if that it works. That'll rule out the memory cause.
Also, did you edit the range to actual data e.g. A2:A2000 not A:A. Don't use the entire column.

 

[Fluff]

Another option if you just want a rank is

=LET(f,FILTER(A2:E3000,A2:A3000<>""),XMATCH(TAKE(f,,1),TAKE(SORT(f,{2,5},-1),,1)))