Formula to read grade, competition score, and place 1st-5th to show winers name.

[Tatonka33]

On The attached form I would like to have a formula that will read the scores in Column H on Sheet 2 and then show the names of 1st-5th place by grade in Columns K and L on sheet 2. All information caries over from sheet 1 and sums the Speech Score on Sheet 2 Column H. I was trying to combine an index and Countif formula, but have had issues. Can any one help?

[martindwilson]

here is one method with conditional ranking

[Tatonka33]

Thank you Martin,
When one of the scores is a tie with the same grade one of the names will not show. Do you know another way? Maybe a countif and index? Thanks for your help.

[Jean.P28]

have change names

[Tatonka33]

Thank you Jean,
The only problem with your formula is that it does not seperate the grade. The table you put your formula will only be 9th graders. I think a countif formula would work, but I'm having problems getting it to work. What would you think?

[martindwilson]

oops forgot about ties try this

[Tatonka33]

If tied it leaves second place blank, maybe an Array will help? Thanks for the help Martin.

[martindwilson]

doesnt in the second workbook i posted looks fine to me
oops looks like i posted the wrong one

[Tatonka33]

Hi Martin,
In your attachment the rank will not change if the score changes. I changed 9th Grade Amy to only have 2 points and she remained in first place. Any ideas?

[martindwilson]

again an error replace > with <
=E3&"-"&SUMPRODUCT(--($E$3:$E$14=E3),--($E3+ROW(E3)/1000000<$E$3:$E$14+ROW($H$3:$H$14)/1000000))+1

[Jean.P28]

here a update

[Tatonka33]

Thank you All!