Ranking a list with conditions

Good Evening,

I was wondering if I could get some help.

I am putting together an excel spreadsheet where I need to rank scores of students who are auditioning for an honor orchestra. However, this audition will also count toward reaching the all-state orchestra level. Students have already registered as one of the following...honor orchestra only(HO), honor orchestra and all-state(BOTH), or all-state only(AS).

All the students are listed on an excel database where their scores will be entered and tabulated. Then, using the RANK function, the overall ranking of their scores will be placed in a column so we can easily determine the order for their seats for only the Honor Orchestra.

However, I would like to not include in the ranking the students who are only auditioning for the all-state level. I have a column set aside for listing which group they are auditioning for. I used the following formula:

=IF(X5="AS"," ",(RANK(T5,$T$5:$T$49)))

This ranks the students, and leaves a blank for those students who are not eligible for the honor orchestra. This creates the problem, however, that any number left blank is still included in the order. Thus, the ranking returned may look like this....1,2,3,_,_,_,7,8.

We have a large number of students auditioning, so it will be difficult to determine the true ranking with these values just not printed in the cell.

My question is:

Is there a function (maybe COUNTIF?) that can be used to rank the students who meet the condition of auditioning for either the Honor Orchestra or Both but NOT the all-state orchestra only?

Thank you in advance for ANY help you can give me.

For whatever reason, the first one you posted is listing all of the results as 1, except for the AS ones which are empty.

Would there be a reason for that...probably something I am doing?

I figured out what I did...I transposed some numbers on the formula.

I appreciate your help VERY much! I would not have figured this out without your help!