MAXIFS and MINIFS with a condition
MAXIFS returns the largest value in a range among the rows that meet your conditions. MINIFS does the same for the smallest.
=MAXIFS(C2:C100,A2:A100,"West")The sheet: Column A is Region and column C is Deal size. You want the largest deal in the West region.
How it works, step by step
- 01C2:C100 is the column to take the largest value from.
- 02A2:A100,"West" limits it to rows where Region is West.
- 03Add more range and criterion pairs to apply more conditions.
The same logic in four tools
=MAXIFS(C2:C100,A2:A100,"West")=MAXIFS(C2:C100,A2:A100,"West")SELECT MAX(deal_size)
FROM deals
WHERE region = 'West';largest = df.loc[df["region"] == "West", "deal_size"].max()Mistakes that break it
- !MAXIFS needs Excel 2019 or Microsoft 365. Older versions use an array formula with MAX and IF.
- !If no row matches, MAXIFS returns 0, not an error, which can be mistaken for a real value.
- !The ranges must be the same size.
- !To find which row holds the maximum, combine it with MATCH or XLOOKUP.
Common questions
- How do I get the date of the largest deal?
- Use =XLOOKUP(MAXIFS(C2:C100,A2:A100,"West"),C2:C100,B2:B100) if deal sizes are unique within the sheet.
- What about the smallest?
- Replace MAXIFS with MINIFS using the same arguments.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.