Ranking Amounts Based on Two Conditions

[legalhustler]

I want to rank numbers based on two conditions, column A has to be >=5.0 and column B has to be >= $100,000,000. What formula would I use in column C so once it meets those conditions it should rank the amounts (column B). If the conditions are not met then the rank should ignore the amounts in column B.

 

[legalhustler]

Here is an example of my data set and what the results should look like:
A1 - 5.0
A2 - 2.0
A3 - 10.5
A4 - 6.7
A5 - 7.5

B1 - $120,000,000
B2 - $110,000,000
B3 - $105,000,000
B4 - $90,000,000
B5 - $125,000,000

Once both conditions are met the formula in column C should give the following rank order based on the amounts in column B. Cell C2 and C4 is empty because both conditions were not met and therefore not considered in the rank order:

C1 - 2
C2 - (empty)
C3 - 3
C4 - (empty)
C5 - 1

What formula would I use to get the the above results in column C?

 

[legalhustler]

Anyone??

 

[Marcelo Branco]

Maybe...

C1 copied down
=IF(AND(A1>=5,B1>=100000000),COUNTIFS(A$1:A$5,">=5",B$1:B$5,">=100000000",B$1:B$5,">"&B1)+1,"")

M.

 

[legalhustler]

Maybe...

C1 copied down
=IF(AND(A1>=5,B1>=100000000),COUNTIFS(A$1:A$5,">=5",B$1:B$5,">=100000000",B$1:B$5,">"&B1)+1,"")

M.

That worked beautifully. One question, in the COUNTIFS range criteria I can not use abs(B$1:B$5)? It prompts a error message. I have to create a helper column to get the absolute value then reference the criteria range in the COUNTIFS formula?

 

[Marcelo Branco]

You cannot use ABS(B$1:B$5) in COUNTIFS, but this should work without helper columns

=IF(AND(A1>=5,ABS(B1)>=100000000),SUMPRODUCT(--(A$1:A$5>=5),--(ABS(B$1:B$5)>=100000000),--(ABS(B$1:B$5)>ABS(B1)))+1,"")

Try it.

M.

 

[legalhustler]

You cannot use ABS(B$1:B$5) in COUNTIFS, but this should work without helper columns

=IF(AND(A1>=5,ABS(B1)>=100000000),SUMPRODUCT(--(A$1:A$5>=5),--(ABS(B$1:B$5)>=100000000),--(ABS(B$1:B$5)>ABS(B1)))+1,"")

Try it.

M.

Aside from the first rank, the rest appear to be +1 more than it should be. However, If I remove the +1 from the end of your formula all the ranks would be correct except the first one. I'm not sure what's causing this.

Below is my formula, where column F is the percent amount (5.0, 2.0, 10.5, etc) and column E is the dollar amount.

=IF(LEFT(A11,5)="Total","",IF(AND(ABS(F11)>=5,ABS(E11)>=100000000),SUMPRODUCT(--(ABS(F$11:F$24)>=5),--(ABS(E$11:E$24)>=100000000),--(ABS(E$11:E$24)>ABS(E11)))+1,""))

 

[legalhustler]

Aside from the first rank, the rest appear to be +1 more than it should be. However, If I remove the +1 from the end of your formula all the ranks would be correct except the first one. I'm not sure what's causing this.

Below is my formula, where column F is the percent amount (5.0, 2.0, 10.5, etc) and column E is the dollar amount.

=IF(LEFT(A11,5)="Total","",IF(AND(ABS(F11)>=5,ABS(E11)>=100000000),SUMPRODUCT(--(ABS(F$11:F$24)>=5),--(ABS(E$11:E$24)>=100000000),--(ABS(E$11:E$24)>ABS(E11)))+1,""))

I see what the problem is. There is a percent and amount for the "Total" line that is throwing the rank off by 1. Anyway for the formula to totally disregard the "Total" line (residing in row E15) percent and amount in the rank?

 

[legalhustler]

Got it to work! Used the below formula. Thanks again MB.

=IF(LEFT(A11,5)="Total","",IF(AND(ABS(F11)>=5,ABS(E11)>=100000000),SUMPRODUCT(--(LEFT($A$11:$A$24,5)<>"Total"),--(ABS(F$11:F$24)>=5),--(ABS(E$11:E$24)>=100000000),--(ABS(E$11:E$24)>ABS(E11)))+1,""))