# MAXIFS & MINIFS by group (Excel lesson 38 of 45)

> The biggest and smallest value for each group, without sorting or filtering.

Source: https://spreadsheetpractice.com/learn/maxifs-minifs-by-group · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Analysis"
- **Time:** about 8 minutes, 2 graded exercises
- **Functions:** `MAXIFS`, `MINIFS`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=MAXIFS($C$2:$C$10,$A$2:$A$10,A12)`: Largest deal in the region

## How it works

`MAXIFS(max_range, criteria_range, criteria)` returns the largest value among the rows that meet the condition, and `MINIFS` returns the smallest. They are the siblings of SUMIFS and AVERAGEIFS.

Put the groups in a column and use one formula for each measure: `=MAXIFS($C$2:$C$10,$A$2:$A$10,A12)` looks at the deal sizes for the region named in A12. Add more criteria pairs for more conditions.

## Syntax

| Formula | What it does |
|---|---|
| `=MAXIFS($C$2:$C$10,$A$2:$A$10,A12)` | Largest deal in the region |
| `=MINIFS($C$2:$C$10,$A$2:$A$10,A12)` | Smallest deal in the region |
| `=MAXIFS(C2:C10,A2:A10,"East",C2:C10,"<20000")` | Largest East deal under 20,000 |

## Watch out for

- The max range comes first, then criteria range and criteria, in pairs.
- All ranges must be the same size.
- If nothing matches, MAXIFS returns 0, not an error.

## The exercises

Solve them in the live grid at [MAXIFS & MINIFS by group](https://spreadsheetpractice.com/learn/maxifs-minifs-by-group). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Largest deal by region.** The regions are listed in A13:A15. In B13, find the largest deal size for each one, using the data in A2:C10. Fill down to B15.
2. **Smallest deal by region.** In C13, find the smallest deal size for each region the same way. Fill down to C15.

Previous: [Median, quartiles & outliers](https://spreadsheetpractice.com/learn/median-quartiles-outliers) · Next: [Loan payments with PMT](https://spreadsheetpractice.com/learn/pmt-loan-payments)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
