Rank with preceeding criteria

[tbecker3]

Hi,
I need a Rank function which the inclusion of Rank is based on weather another column in the file is populated with an X.
So, Rank column AF, if Column C contains an X.
Thanks.

 

[Aladin Akyurek]

Hi,
I need a Rank function which the inclusion of Rank is based on weather another column in the file is populated with an X.
So, Rank column AF, if Column C contains an X.
Thanks.

In AG2 enter and copy down:

=COUNTIFS($C$2:$C$400,"X",$AF$2:$AF$400,">"&AF2)+1

 

[tbecker3]

Doesn't work. It is not identifying that C is blank. Would an If C <> X, then Rank AF work?

 

[Aladin Akyurek]

Doesn't work. It is not identifying that C is blank. Would an If C <> X, then Rank AF work?

Try to post 5 rows from C, 5 rows from AF, and the ranking that must obtain from this sample of records.

 

[tbecker3]

A B Rank of column B if A = X
135
1
X 8 1
X 213 3
X 378 5
X 54 2
X 264 4
20
58

Note the rows without an X are not included in the Rank in column C, essentially they are skipped for the Ranking. Is there a function for this? Thank You!

 

[Aladin Akyurek]

A B Rank of column B if A = X
135
1
X 8 1
X 213 3
X 378 5
X 54 2
X 264 4
20
58

Note the rows without an X are not included in the Rank in column C, essentially they are skipped for the Ranking. Is there a function for this? Thank You!

In AG2 enter and copy down:

=IF($C2="X",COUNTIFS($C$2:$C$10,"X",$AF$2:$AF$10,"<"&AF2)+1,"")

 

[tbecker3]

Yeah, much closer! Results are returning a 1 when AF is blank. Is there a way to eliminate consideration in Rank if AF is blank? If I change the last part to "-1" will the results change to descending order? That's how my boss likes it!

 

[Aladin Akyurek]

Yeah, much closer! Results are returning a 1 when AF is blank. Is there a way to eliminate consideration in Rank if AF is blank? If I change the last part to "-1" will the results change to descending order? That's how my boss likes it!

Ascending...

=IF($C2="X",IF(ISNUMBER($AF2),COUNTIFS($C$2:$C$10,"X",$AF$2:$AF$10,"<"&AF2)+1,""),"")

Descending...

=IF($C2="X",IF(ISNUMBER($AF2),COUNTIFS($C$2:$C$10,"X",$AF$2:$AF$10,">"&AF2)+1,""),"")

 

[tbecker3]

You are AWESOME! Thank You So much.

 

[Aladin Akyurek]

You are AWESOME! Thank You So much.

Glad to help.