Rank+ Countif

New Contributor

Hello everyone,

I am new to Microsoft Tech Community. I hope you all are doing well and are safe.

The issue I am facing using rank and count if function. I want a multiple criteria to get a rank. Initial stage i am getting rank ok to tie the break. But if again tie then another criteria i want to imply then i am not getting the absolute rank. I am adding the excel file for your better understanding.

 

thanks in advance

6 Replies

@MdAhad42 

I cannot follow exactly what your plan is (the translation is not the yellow of the egg either), but as far as I could understand, you can use the filter function to adjust again and again from the changing ranging. 

 

I added the filter function to your file.

 

Hope this information will help you.

 

Thank you for your patience and time.

 

Nikolino

I know I don't know anything (Socrates)

This didnt help me. I want to tie the break but when i get tie, i implement another criteria with countif didnt work out. Only for the 1st 2nd and 3rd rank are worked with my approach. I need all the ranks are working like this three

@NikolinoDE 

@MdAhad42 

The following shows a pattern for ranking against multiple criteria

= 1 + COUNTIFS(criterion1,">"&criterion1)
    + COUNTIFS(criterion1,criterion1, criterion2,">"&criterion2)
    + COUNTIFS(criterion1,criterion1, criterion2,criterion2, criterion3,">"&criterion3)

Clearly, it is possible to use direct cell referencing rather than defined names if you believe that is good and the behaviour of the array formulas differs between 365 and legacy systems.  Again, there are slight changes needed to use the formula within an Excel Table.

@MdAhad42 

Just for entertainment, I used Lambda functions within Excel 365 

image.png

to define a formula based upon the Lambda function

= RANKλ(range1,0,range2,0,range3,1)

 

I think 0,05% of the users have Lambda at the moment ….
True, but I can still seed the idea that change is possible! My guess would be that many spreadsheet users would not even recognise the solution as an Excel formula.