Ranking with TRUE or FALSE and without Skipping numbers (NO DUPLICATES)

[Masha92]

Hi everyone,

I am trying to rank data with conditions.

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64, align: center"]TRUE[/TD]
[TD="class: xl67, width: 64, align: center"] TRUE[/TD]
[TD="width: 64, align: right"]5.46E+08[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl67, align: center"] FALSE[/TD]
[TD="align: right"]31263409[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl67, align: center"] TRUE[/TD]
[TD="align: right"]58662625[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl67, align: center"] TRUE[/TD]
[TD="align: right"]2.36E+08[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl67, align: center"] TRUE[/TD]
[TD="align: right"]23933559[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl67, align: center"] TRUE[/TD]
[TD="align: right"]44886052[/TD]
[/TR]
</tbody>[/TABLE]

When i rank using sumproducts or countif... it skips numbers. so ideally i get,

[TABLE="width: 282"]
<tbody>[TR]
[TD="align: center"]TRUE[/TD]
[TD="align: center"] TRUE[/TD]
[TD="align: right"]545681153[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="align: center"] TRUE[/TD]
[TD="align: right"]31263409[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="align: center"] TRUE[/TD]
[TD="align: right"]58662625[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="align: center"] FALSE[/TD]
[TD="align: right"]235698268[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"] TRUE[/TD]
[TD="align: center"] TRUE[/TD]
[TD="align: right"]23933559[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="align: center"] FALSE[/TD]
[TD="align: right"]44886052[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
My formula is
<u$3:u$8) countif(u$3:u$8,u$3:u$8&""))+1)<u$3:u$8)="" countif(u$3:u$8,u$3:u$8&""))+1)
<u>IF (AND(T3,S3), SUMPRODUCT((U3<U$3:U$8) / COUNTIF (U$3:U$8,U$3:U$8&"")) +1)</u>
<u$3:u$8) countif(u$3:u$8,u$3:u$8&""))+1)
Any help on this? I cannot afford skipping numbers. I.e Number 3 in the previous example should be 2.

Thanks,
Masha</u$3:u$8)></u$3:u$8)>

 

[Special-K99]

Your formula is not displaying correctly.
Places spaces before and after any < > characters in your formula when posting to the forum.

 

[Masha92]

Your formula is not displaying correctly.
Places spaces before and after any < > characters in your formula when posting to the forum.

Thank you so much for the tip!

=IF(AND(W2,V2),SUMPRODUCT((X2 < X$2:X$7)/COUNTIF(X$2:X$7,X$2:X$7&""))+1)

 

[Masha92]

I dont have to use this formula. If you have a formula that can solve this issue, you would be a life saver. And not with VBA, I already did it and got rejected at work. It has to be macro free template!

 

[Special-K99]

Your IF condition is incomplete.

IF(condition, value if true, value if false)

Your formula
=IF(AND(W2,V2),SUMPRODUCT((X2 < X$2:X$7)/COUNTIF(X$2:X$7,X$2:X$7&""))+1)

condition = AND(W2,V2) = So W2 has to be TRUE and V2 has to be TRUE
value if true = SUMPRODUCT((X2 < X$2:X$7)/COUNTIF(X$2:X$7,X$2:X$7&""))+1
value if false = Nothing supplied, that's why you're getting the default of FALSE because one or both of W2 V2 is FALSE

 

[Special-K99]

Hang on, I see what you mean.

You only want to RANK if both conditions are TRUE but it's ranking value where V2 or W2 are FALSE

I understand now.

Thinking...

 

[Masha92]

Hang on, I see what you mean.

You only want to RANK if both conditions are TRUE but it's ranking value where V2 or W2 are FALSE

I understand now.

Thinking...

yes exactly, the false condition doesnt matter really. I just need to rank the true only and without skipping the numbers..

 

[Special-K99]

Not the best way but this looks like it's working

=IFERROR(COUNTIFS(V$2:V$7,TRUE,W$2:W$7,TRUE,IF(V2*W2=1,X$2:X$7), ">"&X2)+1,"")
and copy down the column

This may well be an array formula but works without entering it as one.
Might be best to enter some test data just to confirm that.

Googling RANK IF comes up with COUNTIFS solutions

 

[Masha92]

It works.. it works!!! and not array!! Hurray!

Thank you so much. I am grateful.

 

[Special-K99]

You're welcome, that was a tough one! Very challenging.