Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
MINIFS / MAXIFS
AggregationThe smallest or largest value that meets your conditions.
Difficulty
1What is it?
MINIFS and MAXIFS return the minimum or maximum of a range, but only across the rows that satisfy every condition you give them — the conditional cousins of MIN/MAX. Reach for them to find the earliest open invoice, the largest exposure for one customer, or the lowest margin in a category, without a helper column.
2What it looks like
MAXIFS(max_range, criteria_range1, criteria1, …)- max_range
- The numbers to take the min/max of.
- criteria_range1
- The column to test.
- criteria1
- The condition the row must meet to be considered.
3When you use it
- Largest open balance for a single customer.
- Earliest (MIN) due date among unpaid invoices.
- Highest and lowest price within one product line.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Pull the min or max balance for one customer — the conditional cousin of MIN/MAX.
=MAXIFS(B2:B6, A2:A6, "Acme")$5,400| A | B | C | |
|---|---|---|---|
| 1 | Customer | Balance | Result |
| 2 | Acme | $1,200 | $5,400 |
| 3 | Acme | $5,400 | |
| 4 | Globex | $800 | |
| 5 | Acme | $300 | |
| 6 | Globex | $2,100 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
Returns 0No rows match the criteria.Fix: 0 is the documented 'nothing matched' result — test the count first, or wrap in IF.
#VALUE!max_range and a criteria_range differ in size.Fix: Make all ranges the same height.
#NAME?You're on Excel 2016 or earlier.Fix: MINIFS/MAXIFS need Excel 2019+ — use an array MIN(IF(…)) instead.
6Better functions & alternatives
- MIN / MAX — Unconditional — the smallest or largest of a whole range.
- AGGREGATE — Can take a conditional min/max and also ignore error cells.
- MIN(IF(…)) array — The pre-2019 pattern, entered as an array formula.
Want MINIFS / MAXIFS already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →