COUNTIF() & last row used

[FreakyGirl]

hello

i have a list of names i rank to put them in alphabetical order.

=IF(BB3="","",COUNTIF($BB$3:$BB$150,"<="&BB3))

the list of names gets longer & longer which means means the BB$152 constantly needs to be manually adjusted each time new names are added.

the ranking for the name in BB3 is currently 88. but if you change the B$152 to B$182 it will change their ranking to 105.

rather than monitoring it i have a formula that will tell me the last row with a name but i dont know how to combine the 2 formulas together or reference the cell containing the forumla.

the formula to find the last row with a name is located in AZ3:

=LOOKUP(2,1/(BA:BA<>""),ROW(BA:BA))

help is always appreciated.

thanks,
FreakyGirl

 

[Hui]

Integrate the two formula as shown in BA3

BA3: =IF(BB3="","",COUNTIF(INDIRECT("$BB$3:$BB$"&LOOKUP(2,1/(BA:BA<>""),ROW(BA:BA))),"<="&BB3))

copy down

 

[bosco_yip]

Or...........

BA3, copied down :

=IF(BB3="","",COUNTIF(BB$3:INDEX(BB:BB,MATCH("*",BB:BB,-1)),"<="&BB3))

Regards
Bosco

 

[FreakyGirl]

Thanks to you both so much! Works perfectly!

Have a great day!
FreakyGirl