display lowest 4 values from 6

[sdavis79]

I am looking to display the lowest 4 placing values that meet a team criteria. The input looks like this:

TEAM Placing

ABU - Ind
HC - Ind
STU 1
STU 2
MTA 3
MTA 4
MTA 5
USA 6
MTA - Ind
ABU - Ind
HC - Ind
USA 7
STU 8
STU 9
MTA 10
MTA 11
MTA 12
MTA - Ind
STU 13
USA 14
STU 15
ABU - Ind
USA 16


The output must look like this:

Place Team Score 1st runner 2nd runner 3rd runner 4th runner
1 STU 20pts 1 2 8 9
2 MTA 22pts 3 4 5 10
3 USA 43pts 6 7 14 16

But I cannot seem to find a combination of formulae that extracts the top 4 placings by team. Any ideas?

[daddylonglegs]

Assuming your data, teams and placings, is in A2:B24 then, with a specific team name in D2 you can use this formula in F2

=SMALL(IF($A$2:$A$24=$D2,$B$2:$B$24), COLUMNS($F2:F2))

This is an "array formula" which must be confirmed with CTRL+SHIFT+ENTER, then copy across to I2

Get the total points in E2 with

=SUM(F2:I2)

You can add additional teams in column D and copy all formulas down

[sdavis79]

This seems to work quite well if the placings are all sequential, but does not seem to work if there are gaps (empty cells). Is there a way around this?

[daddylonglegs]

If you have blanks in the "Placings" column, B2:B24 in my example, change formula in F2 to

=SMALL(IF($A$2:$A$24=$D2,IF ($B$2:$B$24<>"",$B$2:$B$24)),COLUMNS($F2:F2))

[daddylonglegs]

...also if you want to prevent errors in the scenario where a team has less than 4 placings make a further change to

=IF(COLUMNS($F2:F2)>SUM(($A$2:$A$24=$D2)*(ISNUMBER($B$2:$B$24))),"",SMALL(IF($A$2:$A$24=$D2,IF($B$2:$B$24<>"",$B$2:$B$24)), COLUMNS($F2:F2)))

[sdavis79]

This is brilliant - excatly what I am looking for. However there is one more item that needs to be figured out. I have another forumla which is giving the values in both columns A & B, so some of them are 'empty' and showing #N/A. So those formulae seem to be given a value of zero in a set of data with #N/A is present. Is there a way to tell the formulae to ignore #N/A entries?