Excel MAXIFS and MINIFS — Find Max/Min with Conditions (with Examples)

What does it do?

MAXIFS returns the maximum value from a range where one or more conditions are all true. MINIFS returns the minimum. They are the conditional equivalents of MAX and MIN — letting you find the best or worst result within a specific subset of your data without filtering or sorting.

Syntax

=MAXIFS(max_range, criteria_range1, criteria1, ...)

Argument	Description
=MAXIFS(max_range, criteria_range1, criteria1, ...)
max_range required	The range to find the maximum from.
criteria_range1 required	The column to check the first condition against.
criteria1 required	The first condition to match. All conditions must be true.

Real examples

Example 1

Highest sales in one region

=MAXIFS(C2:C100, B2:B100, "North")

Finds the largest sale where the region is North.

Example 2

Lowest score in one category

=MINIFS(D2:D100, A2:A100, "Category A")

Finds the lowest value in Category A.

Example 3

Best result above a threshold

=MAXIFS(C2:C100, B2:B100, "North", C2:C100, ">1000")

Highest North sale that also exceeds 1000.

Example 4

Most recent date in a category

=MAXIFS(A2:A100, B2:B100, "Active")

Finds the latest date where status is Active.

Example 5

Dynamic lookup with cell reference

=MAXIFS(C:C, B:B, E1)

E1 contains the region to search. Change E1 for a different result.

FAQ

What Excel version do MAXIFS and MINIFS require?

Excel 2019 or Microsoft 365. In older versions, use AGGREGATE or array formulas with MAX and IF.

Can MAXIFS return a name instead of a number?

Not directly. Use INDEX MATCH with MAXIFS: =INDEX(A2:A100, MATCH(MAXIFS(C2:C100,B2:B100,"North"),C2:C100,0)) to return the name of the top performer.

What if no cells match the criteria?

MAXIFS returns 0 if no rows match. Test for this with IF: =IF(MAXIFS(...)=0,"No match",MAXIFS(...)).

MAXIFS and MINIFS in performance analysis

Before MAXIFS existed (it arrived in Excel 2019), finding the conditional maximum required an array formula: =MAX(IF(B2:B100="North",C2:C100)) entered with Ctrl+Shift+Enter. MAXIFS replaces this with a cleaner, non-array formula that is easier to read and faster to calculate on large datasets.

A common use in sales analysis: find the highest individual sale for each region. Put each region name in column E, then in F2: =MAXIFS($C$2:$C$100,$B$2:$B$100,E2). Copy down to get the top sale for every region in one set of formulas — no filtering, no sorting, no manual checking.

MINIFS follows the same logic but finds the minimum. Together they give you the full range for each group. Combined with AVERAGEIFS you get mean, max and min for every category in seconds.

💡 MAXIFS returns 0 when no match

If no rows match your criteria, MAXIFS returns 0 — not an error. Check for this in your results: =IF(MAXIFS(...)=0,"No data",MAXIFS(...)) to distinguish a genuine zero result from a no-match situation.

Practise this formula live

ExcelPro has exercises covering this formula across multiple tracks. Free to start.

Try exercises →

Related formulas

SUMIFS COUNTIFS AVERAGEIFS LARGE IF VLOOKUP

Excel MAXIFS & MINIFSexplained simply