How to get the top 3 values for each group in Excel
Excel formula
=SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),{1,3},{1,-1})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}).
- Result on the example
- a 9-row list that spills from E3 in
E3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.
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.
=SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),{1,3},{1,-1})Result{9 rows × 3 cols} "East", "Sam Cole", 57300, "East", …
A2:C1413 rows × 3 cols: "East", "Ana Ruiz", $48,200, "East", …A range: 13 rows × 3 columns.
A2:A1413 rows × 1 col: "East", "East", "West", "East", …A range: 13 cells in one column.
C2:C1413 rows × 1 col: $48,200, $39,100, $61,750, $57,300, …A range: 13 cells in one column.
">"&C2:C14{13 rows × 1 col} ">48200", ">39100", ">61750", ">57300", …The & operator joins as text.
COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14){13 rows × 1 col} 1, 3, 0, 0, …COUNTIFS: Counts rows that meet every condition.- criteria_range1
A2:A1413 rows × 1 col: "East", "East", "West", "East", … - criteria1
A2:A1413 rows × 1 col: "East", "East", "West", "East", … - criteria_range2
C2:C1413 rows × 1 col: $48,200, $39,100, $61,750, $57,300, … - criteria2
">"&C2:C14{13 rows × 1 col} ">48200", ">39100", ">61750", ">57300", …
- criteria_range1
F13The value in F1.
COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1{13 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The < operator tests less than.
FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1){9 rows × 3 cols} "East", "Ana Ruiz", 48200, "West", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C1413 rows × 3 cols: "East", "Ana Ruiz", $48,200, "East", … - condition1
COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1{13 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …
- range
{1,3}{1 row × 2 cols} 1, 3An array of values.
{1,-1}{1 row × 2 cols} 1, -1An array of values.
SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),{1,3},{1,-1}){9 rows × 3 cols} "East", "Sam Cole", 57300, "East", …SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1){9 rows × 3 cols} "East", "Ana Ruiz", 48200, "West", … - sort_column
{1,3}{1 row × 2 cols} 1, 3 - is_ascending
{1,-1}{1 row × 2 cols} 1, -1
- range
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)<F1),1,TRUE,3,FALSE)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORT(FILTER(A2:C14,COUNTIFS(A2:A14,A2:A14,C2:C14,">"&C2:C14)=0),1) | spills 3 × 3Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get the top 3 values for each group
E3, return the top F1 reps by sales in each region, grouped by region and highest first within each.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.