How to find the highest value for each category in Excel
Excel formula
=HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))List the categories with UNIQUE and get each maximum with MAXIFS: =HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13))).
- Result on the example
- a 3-row list that spills from E2 in
E2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
UNIQUE returns East, West and North. Given that list as its criteria, MAXIFS returns the largest sale for each one, and HSTACK sets the two columns side by side. The same pattern works with MINIFS for the smallest value or AVERAGEIFS for the average.
In Google Sheets, QUERY's group by with max() is the usual way to do this.
=HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)))Result{3 rows × 2 cols} "East", 4200, "West", 3880, …
A2:A1312 rows × 1 col: "East", "West", "East", "North", …A range: 12 cells in one column.
UNIQUE(A2:A13){3 rows × 1 col} "East", "West", "North"UNIQUE: Distinct rows of a range. Spills.- array
A2:A1312 rows × 1 col: "East", "West", "East", "North", …
- array
C2:C1312 rows × 1 col: $4,200, $3,150, $1,900, $2,620, …A range: 12 cells in one column.
UNIQUE(A2:A13){3 rows × 1 col} "East", "West", "North"UNIQUE: Distinct rows of a range. Spills.- array
A2:A1312 rows × 1 col: "East", "West", "East", "North", …
- array
MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)){3 rows × 1 col} 4200, 3880, 2960MAXIFS: Largest value that meets every condition.- max_range
C2:C1312 rows × 1 col: $4,200, $3,150, $1,900, $2,620, … - criteria_range1
A2:A1312 rows × 1 col: "East", "West", "East", "North", … - criteria1
UNIQUE(A2:A13){3 rows × 1 col} "East", "West", "North"
- max_range
HSTACK(UNIQUE(A2:A13),MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13))){3 rows × 2 cols} "East", 4200, "West", 3880, …HSTACK: Places ranges side by side.- array1
UNIQUE(A2:A13){3 rows × 1 col} "East", "West", "North" - array2
MAXIFS(C2:C13,A2:A13,UNIQUE(A2:A13)){3 rows × 1 col} 4200, 3880, 2960
- array1
In Google Sheets
Google Sheets needs a different formula: =QUERY(A1:C13,"select A, max(C) group by A label max(C) 'Top sale'",1). QUERY adds a header row and lists the regions A to Z; the maximums are the same.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =LET(r,UNIQUE(A2:A13),HSTACK(r,MAXIFS(C2:C13,A2:A13,r),MINIFS(C2:C13,A2:A13,r)))Excel only | spills 3 × 3Different result | Add more statistics as extra columns. A third column with each region's smallest sale. |
Common mistakes
- MAX on its own ignores the category:
MAX(C2:C13)is the largest sale overall. - MAXIFS needs Excel 2019 or later. In older versions, use
MAX(IF(A2:A13="East",C2:C13))as an array formula.
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.
Find the highest value for each category
E2, list each region once with its largest single sale next to it.Questions
How do I show who made the top sale in each region?
Return the matching rows instead: =FILTER(A2:C13,C2:C13=MAXIFS(C2:C13,A2:A13,A2:A13)).
How do I get the lowest value per category?
Use MINIFS in the same place: MINIFS(C2:C13,A2:A13,UNIQUE(A2:A13)).
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.