MAXIFS and MINIFS: Highest and Lowest With Conditions

Excel MAXIFS finds the highest value among the rows that meet your conditions, such as the top salary in Marketing. MINIFS finds the lowest, such as the lowest Junior salary in Finance. Both work like SUMIFS, with the numbers first and then range/criteria pairs. This guide covers the syntax, worked examples, the surprising 0 result, and what to use in Excel 2016 and older.

What do MAXIFS and MINIFS do?#

MAXIFS and MINIFS look only at rows that match every condition, then return the largest or smallest number from those rows. The syntax:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Version note: MAXIFS and MINIFS need Excel 2019, Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. In Excel 2016 and older they show #NAME?.

The examples use this staff table in A1:E9:

How to use MAXIFS in Excel#

Put the numbers first, then the column to check and its criteria. For the top salary in Marketing:

=MAXIFS(D2:D9,B2:B9,"Marketing")

The result is 84,000 (Lara). Excel looks at the two Marketing rows, 84,000 and 52,000, and returns the larger.

Here is what plain MAX would do: =MAX(D2:D9) returns 118,000, the top salary in the whole company. MAXIFS narrows it to one group.

How to use MINIFS in Excel#

MINIFS has exactly the same shape and returns the smallest value. For the lowest Engineering salary:

=MINIFS(D2:D9,B2:B9,"Engineering")

The result is 68,000 (Felix). Comparisons work too. The lowest salary among people with 5 or more years:

=MINIFS(D2:D9,E2:E9,">=5")

Noor, Ivy, Lara and Oscar match, so the result is 74,000 (Oscar).

MAXIFS and MINIFS with multiple conditions#

Add one range/criteria pair per condition. A row must match every pair to be included. The highest Engineering salary for people with fewer than 5 years:

=MAXIFS(D2:D9,B2:B9,"Engineering",E2:E9,"<5")

Felix (1 year) and Milo (4 years) match, so the result is 95,000.

To fill a summary, point the criteria at a cell. Put Finance, Marketing and Engineering in G8:G10, and type this in H8 to find the lowest Junior salary in each department:

=MINIFS($D$2:$D$9,$B$2:$B$9,G8,$C$2:$C$9,"Junior")

Fill down to H10 (Windows: Ctrl+D; Mac: ⌘D). You get 58,000 for Finance, 52,000 for Marketing and 68,000 for Engineering. The $ signs keep the data ranges fixed while G8 moves down.

To let someone change a limit without editing the formula, join an operator to a cell. If J1 holds 5, this finds the top salary among people with fewer than 5 years:

=MAXIFS(D2:D9,E2:E9,"<"&J1)

Felix, Kenji, Milo and Nia match, so the result is 95,000. Keep the < in quotes and J1 outside them.

Criteria can also be partial text. "Eng*" matches any department starting with Eng; see how wildcards work in criteria.

How to find who has the top value#

MAXIFS returns the number, not the person. To get the name next to it, look the result up. With the top Marketing salary in H2:

=XLOOKUP(1,(B2:B9="Marketing")*(D2:D9=H2),A2:A9)

The result is Lara. The two tests in brackets each give TRUE or FALSE, and multiplying them gives 1 only where both are true, so the lookup finds the Marketing row with that salary. Checking the department as well matters: a plain lookup of 84,000 could find someone in another department with the same pay. XLOOKUP needs Excel 2021 or Microsoft 365; see how XLOOKUP works.

Why does MAXIFS return 0?#

MAXIFS and MINIFS return 0 when no row matches, not an error. That is easy to miss, because 0 looks like a real answer.

For example, =MAXIFS(D2:D9,B2:B9,"Sales") returns 0 because there is no Sales department. If 0 could be a real value in your data, check that matches exist first:

=IF(COUNTIF(B2:B9,"Sales")=0,"No match",MAXIFS(D2:D9,B2:B9,"Sales"))

This shows "No match" instead of a misleading 0. COUNTIF is the quickest way to confirm how many rows meet a condition.

Other common problems:

• #VALUE!: the ranges are different sizes. Make every range cover the same rows.
• Numbers on the wrong side: =MAXIFS(B2:B9,"Marketing",D2:D9) follows the SUMIF order and fails. The numbers go first.
• Text numbers: salaries stored as text are ignored. Convert them with VALUE.

How to find the latest or earliest date with MAXIFS#

Excel stores dates as numbers, so MAXIFS returns the latest date and MINIFS the earliest. If column A holds order dates and column B holds customer names, =MAXIFS(A2:A100,B2:B100,"Acme") returns Acme's most recent order date. If the cell shows a number like 45700, format it as a date.

To total or count orders between two dates instead, see how to sum by date range with SUMIFS.

What to use in Excel 2016 and older#

Without MAXIFS, use MAX with IF inside it as an array formula, a formula that works on a whole range at once:

=MAX(IF(B2:B9="Marketing",D2:D9))

In Excel 2016 and older, press Ctrl+Shift+Enter instead of Enter, and Excel adds curly brackets around the formula. In Microsoft 365, plain Enter works. IF returns the salary for Marketing rows and FALSE for the others, and MAX ignores the FALSE values, so the result is 84,000. For two conditions, multiply the tests:

=MIN(IF((B2:B9="Engineering")*(C2:C9="Junior"),D2:D9))

That returns 68,000. Like MAXIFS, these return 0 when nothing matches.

Practise it#

MAXIFS and MINIFS are taught together with AVERAGEIF in one hands-on lesson. Practise AVERAGEIF, MAXIFS and MINIFS in a live spreadsheet: find the top Marketing salary, then fill a MINIFS summary of the lowest Junior pay per department.