Need a formula

[Relli]

I want all cells in column cell A that have the name 'wait list' to be ranked by that related column cell B's date in descending order in the related cell C 1 through 15. I do not want to use a pivot table.

 

[Nebu]

Hi:

I think its doable using sumproduct formula could you upload a sample workbook.

Thanks

 

[Busymanjohn]

Try this in Col C ,,, =IF(A2="TBD","",MATCH(B2,LARGE(IF($A$2:$A$15=A2,$B$2:$B$15),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$15,A2)))),0))

I put TBD into some of the cells in Col A as I don't know what else you would have other than "wait list" in Col A ..... the formula is an array formula, so must be confirmed with control + shift + enter instead of just hitting enter key.

 

[Relli]

I tried using that formula but it did not number the dates in order. It put NA# where it did not say waitlist and it left the cell blank where it said waitlist. I uploaded a sample file.

 

[Nebu]

Hi:

Use the following formula.

=IF([@Status]="Enrolled","",SUMPRODUCT(--([@Status]=[Status]),--([@[Waitlist Date]]>[Waitlist Date]))+1)

Thanks