h317,

For your example table, starting in cell A1:

=1+SUMPRODUCT(($A$2:$A$9=A2)*($B$2:$B$9<B2))

HTH,
Bernie
MS Excel MVP


"h317" <h317.20cfcz_1135089902.1786@excelforum-nospam.com> wrote in message
news:h317.20cfcz_1135089902.1786@excelforum-nospam.com...
>
> For the "rank within a flight" to work, I need to create two different
> formulas for row 1 to 4 and 5 to 8 separately. I was trying to create a
> fits-all formulat in the 4th column of the following spreadsheet. I
> tried using array function and it did not work. Please help.
>
> Rank Rank
> Flight Scores Full Field Flight
> A 78 1 1
> A 92 6 4
> A 90 4 3
> A 88 2 2
> B 91 5 2
> B 98 7 3
> B 89 3 1
> B 100 8 4
>
> Please help. Thanks.
>
>
> --
> h317
> ------------------------------------------------------------------------
> h317's Profile: http://www.excelforum.com/member.php...o&userid=17043
> View this thread: http://www.excelforum.com/showthread...hreadid=494871
>