In Excel: use =MAXIFS(max_range, criteria_range, criteria) — it returns the largest value among the rows meeting your conditions, with MINIFS doing the same for the smallest.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
max_range | required | The cells to take the maximum (or minimum) of. |
criteria_range1 | required | The range to test, the same size as max_range. |
criteria1 | required | The condition to apply. |
Related functions
Select the result cell and type =MAXIFS( or =MINIFS(.
Select the column of numbers first, then a comma.
Select the condition column, add a comma, and type the condition in quotes.
Add further range-and-criterion pairs as needed, then press Enter.
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
What this does
MAXIFS and MINIFS return the largest and smallest values among rows that satisfy every condition given. Before Excel 2019 this required an array formula entered with Ctrl+Shift+Enter, which is why so many older workbooks still carry that pattern. Conditions combine with AND only. When nothing matches they return 0 rather than an error, and that is the trap: a legitimate zero and "no rows matched" look identical, so a COUNTIFS guard is worth adding wherever the distinction matters. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. Treat “maxifs function” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected formula shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
Deal values in D2:D500 with regions in A2:A500: =MAXIFS(D2:D500, A2:A500, "West") returns the largest Western deal. The smallest order above a floor: =MINIFS(D2:D500, D2:D500, ">0"). Guarding against the silent zero: =IF(COUNTIFS(A2:A500,"West")=0, "none", MAXIFS(D2:D500, A2:A500, "West")). MAXIFS and MINIFS collapse the old Ctrl+Shift+Enter array idiom into an ordinary formula, which makes conditional extremes readable again. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. If you take one thing from this page on “maxifs function”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
Common mistakes
- Reading the 0 returned when nothing matches as a real minimum or maximum — add a
COUNTIFScheck. - Using them in Excel 2016 or earlier, where they do not exist; there,
MAXwith anIFarray is the fallback. - Expecting
ORlogic across conditions, which is not supported — every criterion must hold.
Frequently asked questions
What do MAXIFS and MINIFS return when nothing matches?
Zero, not an error. Wrap them in a COUNTIFS test if a real zero would be ambiguous in your data.
Which Excel versions have them?
Excel 2019 and later, plus Microsoft 365 and Google Sheets. Earlier versions need an array formula with MAX and IF.
Can I use more than one condition?
Yes — add further criteria-range and criteria pairs. They are combined with AND.