Rank a subset with duplicated values defined by another column

[TCB13]

Hello,

I'm trying to create a column with a rank of values based on a control column. I've posted this in StackOverflow but I feel this forum is more appropriate for the topic.

My data:

(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:

Now how I can do a rank on col D that:

a) Only considers the values if the Control col B has the value 1
b) Also deals with duplicates in the same way.

My end goal is to have this:

In StackOverflow I was suggested the following formula:

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

Unfortunately it doesn't work if I've duplicate values in col A with different control numbers:

Thank you very much.

 

[Marcelo Branco]

Maybe something like this

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

Cell Formulas
Range	Formula
C2:C9	C2	=IF(B2=1,COUNTIFS(B$2:B$9,1,A$2:A$9,">"&A2)+COUNTIFS(B2:B$9,1,A2:A$9,A2),"")

M.

 

[TCB13]

Hi @Marcelo Branco thank you very much! It does work with a limited number of rows.

I can always replace A$9 with A$1048576 and have it sorting the entire sheet.

Thank you.

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.