SheetSense

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.

fx
=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

  1. 01C2:C100 is the column to take the largest value from.
  2. 02A2:A100,"West" limits it to rows where Region is West.
  3. 03Add more range and criterion pairs to apply more conditions.

The same logic in four tools

Excel
=MAXIFS(C2:C100,A2:A100,"West")
Google Sheets
=MAXIFS(C2:C100,A2:A100,"West")
SQL
SELECT MAX(deal_size)
FROM deals
WHERE region = 'West';
Python (pandas)
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.