RANKING Given Multiple Criteria

I need a formula in column BH which ranks the activity in column A given the followin criteria:

Rank by Age (column AJ) - oldest is No. 1.
Column BI = NON CIVILS
Column BB = ISBLANK

Rank 1 to 10 for the above criteria when LEFT(AP2,1)="R"
Rank 1 to 10 for the above criteria when LEFT(AP2,1)="A"
Rank 1 to 10 for the above criteria when LEFT(AP2,1)="G"

Display rank results as R1 0081 03ABCD

I have manually typed in the results.

Thanks if you can help.

Hi
Try this in BH2
=IF(BI2="NON CIVILS",IF(LEFT(AP2,1)="G",IF(COUNTIF(BH$1:BH1,"G*")<10,"G"&COUNTIF(BH$1:BH1,"G*")+1&" "&A2,""),IF(LEFT(AP2,1)="R",IF(COUNTIF(BH$1:BH1,"R*")<10,"R"&COUNTIF(BH$1:BH1,"R*")+1&" "&A2,""),IF(LEFT(AP2,1)="A",IF(COUNTIF(BH$1:BH1,"A*")<10,"A"&COUNTIF(BH$1:BH1,"A*")+1&" "&A2,""),""))),"")

Formula:

=IF(BI2="NON CIVILS",IF(LEFT(AP2,1)="G",IF(COUNTIF(BH$1:BH1,"G*")<10,"G"&COUNTIF(BH$1:BH1,"G*")+1&" "&A2,""),IF(LEFT(AP2,1)="R",IF(COUNTIF(BH$1:BH1,"R*")<10,"R"&COUNTIF(BH$1:BH1,"R*")+1&" "&A2,""),IF(LEFT(AP2,1)="A",IF(COUNTIF(BH$1:BH1,"A*")<10,"A"&COUNTIF(BH$1:BH1,"A*")+1&" "&A2,""),""))),"")

Hi Jose,
Thanks for your quick reply. It works brilliantly except that if there is a date in column BB, it still ranks. Can you make it so it only ranks with column BB is "". Thanks in anticipation. Andy.

Hi
Cell BB58 is not empty. Why BH58 is 58 0041 78SDYR ? I do not see any date in column BB.

Try

Formula:

=IF(ISEMPTY(BB2),"",IF(BI2="NON CIVILS",IF(LEFT(AP2,1)="G",IF(COUNTIF(BH$1:BH1,"G*")<10,"G"&COUNTIF(BH$1:BH1,"G*")+1&" "&A2,""),IF(LEFT(AP2,1)="R",IF(COUNTIF(BH$1:BH1,"R*")<10,"R"&COUNTIF(BH$1:BH1,"R*")+1&" "&A2,""),IF(LEFT(AP2,1)="A",IF(COUNTIF(BH$1:BH1,"A*")<10,"A"&COUNTIF(BH$1:BH1,"A*")+1&" "&A2,""),""))),""))

Hi Jose, Thanks for your help again. 58 was a typo error (sorry). My excel does not recognise ISEMPTY. Have tried replacing with BB2="" but that does not work. Any ideas? Thanks, Andy.

Updated example attached.

Hi @watchouse

I don't see any value in cells in range BB2:BB961. All this cells are empty.
I used my first formula (post #2) and the results are the same you proposed
Every cells in range(BH2:BH961) are equal to cells I backup range(BK2:BK961)
(See the BN2 CSE formula)
What is wrong?

Sorry. I don´t append the file EXAMPLE(3).xlsm

Sorry, I removed all the values in column BB but they do have date values. I need to only rank when there is no date in that cell.

Sorry for the confusion.

Try these

Formula:

=IF(ISBLANK(BB2),IF(BI2="NON CIVILS",IF(LEFT(AP2,1)="G",IF(COUNTIF(BH$1:BH1,"G*")<10,"G"&COUNTIF(BH$1:BH1,"G*")+1&" "&A2,""),IF(LEFT(AP2,1)="R",IF(COUNTIF(BH$1:BH1,"R*")<10,"R"&COUNTIF(BH$1:BH1,"R*")+1&" "&A2,""),IF(LEFT(AP2,1)="A",IF(COUNTIF(BH$1:BH1,"A*")<10,"A"&COUNTIF(BH$1:BH1,"A*")+1&" "&A2,""),""))),""),"")

Hi Jose, It works brilliantly. I don't have a clue how it works, but it does! Thank you very much from myself and the UK's largest water company!