# How to get the top 3 values for each group in Excel

Keep a row when fewer than N rows in its group beat it: `=SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),{1,3},{1,-1})`.

**Excel:** `=SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),{1,3},{1,-1})` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),1,TRUE,3,FALSE))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Region | Rep | Sales |  | Top | 3 |
| East | Ana Ruiz | 48200 |  |  |  |
| East | Lee Chen | 39100 |  |  |  |
| West | Raj Patel | 61750 |  |  |  |
| East | Sam Cole | 57300 |  |  |  |
| North | Kim Ortiz | 44150 |  |  |  |
| West | Mo Diaz | 52400 |  |  |  |
| East | Tom Reyes | 33800 |  |  |  |
| North | Eve Lund | 59900 |  |  |  |
| West | Noor Ali | 41200 |  |  |  |
| North | Ivy Shah | 38650 |  |  |  |
| East | Dev Rao | 45900 |  |  |  |
| West | Ben Ford | 47300 |  |  |  |
| North | Cara Kim | 51800 |  |  |  |

Formula in E3: `=SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),{1,3},{1,-1})`

Result (spills 9 × 3): East, Sam Cole, $57,300; East, Ana Ruiz, $48,200; East, Dev Rao, $45,900; North, Eve Lund, $59,900; North, Cara Kim, $51,800; North, Kim Ortiz, $44,150; West, Raj Patel, $61,750; West, Mo Diaz, $52,400; West, Ben Ford, $47,300

## How it works

For each row, COUNTIFS counts the reps in the same region with higher sales. The top rep in a region has 0 above them, the second has 1 and the third 2, so `<F1` keeps the top three of every region. SORT then groups the regions A to Z and orders each by sales, highest first.

## Other ways

- `=SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)=0),1)`: Use =0 to keep only the single best rep in each region. (One row per region: Sam Cole, Eve Lund and Raj Patel.)

## Common mistakes

- Ties at the cut-off are all kept: two reps tied for third in a region both come back, so a group can show four rows.
- The comparison needs `">"&C2:C14`, joined as text. Writing `">C2:C14"` compares with the literal text and counts nothing.

## Questions

**How do I get the bottom 3 per group?**

Count the smaller values instead: `COUNTIFS(A2:A14,A2:A14,C2:C14,"<"&C2:C14)<F1`.

**How do I rank within each group?**

Add 1 to the same count to get each rep's rank in their region: `=COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)+1`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/top-3-values-for-each-group