MrExcel.com - Your One Stop for Excel Tips & Solutions

How to Create Top List

February 21, 2002 - by Juan Pablo Gonzalez

Richard asks:

MrExcel, I need a formula to add the 9 highest valued cells out of 11 cells. I have a motorcycle racer database set up on Excel 97 and during the series I will count a riders top 9 races (they have to run 8 races to be eligible).

Therefore if a rider races 8 races I will just add all 8 races.

  • If a rider races 9 races I will also add all 9 races.
  • If a rider races 10 races I will add 9 races and "drop" there worst race (lowest value).
  • If a rider races all 11 races I will add the highest 9 valued races and "drop" the worst 2.

If your races are in A1:A11, try with this formula

=IF(COUNT(A1:A11)<8,0,SUM(A1:A11)-(COUNT(A1:A11)>9)*SMALL(A1:A11,1)-(COUNT(A1:A11)>10)*SMALL(A1:A11,2))