MINIFS Function

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. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. For “minifs function”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected formula change, and only then apply the same setup to the full sheet. 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 is what makes a calculation you can defend to a CFO or an auditor useful in real work: repeatable, auditable, and not dependent on memory or luck.

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. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

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. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. Treat “minifs function” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

Common mistakes

• Reading the 0 returned when nothing matches as a real minimum or maximum — add a COUNTIFS check.
• Using them in Excel 2016 or earlier, where they do not exist; there, MAX with an IF array is the fallback.
• Expecting OR logic across conditions, which is not supported — every criterion must hold.

Frequently asked questions