Excel: Rank a subset with duplicated values defined by another column

user_560745 0
Viewed 80

I'm trying to create a column with a rank of values based on a control column like this:

(A)     (B)     (C)
Value   Control Rank
100     1       8
200     1       6
200     1       7
300     0       5
310     0       4
320     1       3
350     1       2
400     1       1

In col C I've used a combination of RANK and COUNTIF in order to get a general rank of all entries that accommodates duplicates nicely:

enter image description here

Now how I can do a rank on col D that only considers the values if the Control col B has the value 1?

My goal is to have this:

enter image description here

Note that I need it to deal with duplicates in the same way.

Thank you.

countif ranking excel
2 Answers

Give a try on below formula-

=IFERROR(MATCH(A2,SORT(FILTER($A$2:$A$9,$B$2:$B$9>0),1,-1),0)+COUNTIF($A$2:A2,A2)-1,"")

enter image description here

user_55147470

Here's a solution for this problem:

=IF(B2=1,COUNTIFS(B$2:B$9,1,A$2:A$9,">"&A2)+COUNTIFS(B2:B$9,1,A2:A$9,A2),"")

One might expand the B$2:B$9 and A$2:A$9 ranges to A$1048576 and B$1048576 respectively in order to have it rank the entire sheet.

user_5607450
Related