MAXIFS in Excel: what it does and how to use it

MAXIFS returns the largest number in a column, but only from the rows that meet the conditions you set. MINIFS does the same for the smallest.

Syntax

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

When to use it

The best result in each region, the highest invoice for one customer, the latest date for one project.

What trips people up

Unlike SUMIFS, the range you are taking the maximum from comes first and the conditions follow. It needs Excel 2019 or later.

Watch it built, step by step

Highest value in each group — The top figure for one category

Questions

Is there a MINIFS as well?

Yes. MINIFS takes exactly the same arguments and returns the smallest matching value instead.

What does MAXIFS return when nothing matches?

0, not an error. If 0 could be a real value in your data, check with COUNTIFS first.

What can I use in older versions of Excel?

MAXIFS arrived with Excel 2019. Before that, =MAX(IF(A2:A100="North",B2:B100)) does the same job, confirmed with Ctrl+Shift+Enter.

Related functions

LARGE — LARGE returns the nth biggest value in a range: the second largest, the third, whichever position you ask for.
SUMIFS — SUMIFS adds up the numbers in one column, but only on the rows that satisfy every condition you give it.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.