# Extract Top N Values in Each Group

1. ## Extract Top N Values in Each Group

Good evening. Please, I need some help.

How can I extract the Top 5 values from each group? I want to figure out who are the Top 5 states from each heat based on the number of points.

2. ## Re: Extract Top N Values in Each Group

Which version(s) of Excel will this need to work on? Your profile, as it stands, is not too helpful.

3. ## Re: Extract Top N Values in Each Group

I am using Excel 2021, thank you.

4. ## Re: Extract Top N Values in Each Group

OK - I think this will work in 2021. In J4 copied down:

=FILTER(\$B\$2:\$B\$57,(\$E\$2:\$E\$57=\$I\$2)*(\$G\$2:\$G\$57=\$I4))

5. ## Re: Extract Top N Values in Each Group

There is a tie for 5th place in Heat 5. How can I fix this so it leaves with only one value? I made this formula to count how many times it occur: =COUNTIFS(\$E\$2:\$E\$57,E2,\$F\$2:\$F\$57,">"&F2)+1

6. ## Re: Extract Top N Values in Each Group

What do you mean by 'only one value'? Not clear (to me).

7. ## Re: Extract Top N Values in Each Group

I believe the fix for this if you don't care about tie breaking criteria is to enter ALI's formula above as an array formula using CTRL + SHIFT + ENTER. If done so correctly this will add { } around your formula.
This will remove the multiple entries for 5th place as it will only show the first one.

8. ## Re: Extract Top N Values in Each Group

Another option is
Formula:
which will return 5 rows. If your data is always sorted descending on the points column, there is no need for the sort

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1