RANKING using Criteria

Joesif

Board Regular
Joined
Feb 23, 2010
Messages
65
Hi All,


I'm wanting to Rank a set of figures in one column but use criteria in another column to pick up the array which is used. I don't want any figures to be ignored so will have multiple rank 1's 2's etc. Would also be handy if I could use multiple criteria.


Thanks

Joe
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Can we see some examples of the data you want to use, for the figures and criteria and how will the criteria affect the ranking
 
Upvote 0
data.png


So basically I'd like to rank all rankings but only rank ratings with the same date, so 105 and 100 will both be rank 1

Thanks
Joe
 
Upvote 0
data2.png


Would you be able to adapt the same formula to use two columns as the criteria, so above 105 100 93 and 88 would all be ranked 1

Thanks
Joe
 
Upvote 0
You are most welcome Joesif.

Please use any of the following options for the same result.


1. =SUMPRODUCT((A2=$A$2:$A$17)*(C2<$C$2:$C$17)*(B2=$B$2:$B$17))+1
2. =SUMPRODUCT(--(A2=$A$2:$A$17),--(C2<$C$2:$C$17),--(B2=$B$2:$B$17))+1
3. =+COUNTIFS($C$2:$C$17,">"&C2,$A$2:$A$17,A2,$B$2:$B$17,B2)+1
 
Last edited:
Upvote 0
Thanks ever so much Ramanan,

I used the Countifs option you gave me above to pick up the extra column.
=COUNTIFS($C$2:$C$17,">"&C2,$A$2:$A$17,A2,$B$2:$B$17,B2)+1

Joe
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top