How to Use MAXIFS and MINIFS in Excel
Use MAXIFS and MINIFS to find the largest or smallest value that meets one or more conditions in Excel, with finance examples and common fixes.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
MAXIFS and MINIFS return the largest or smallest value that meets one or more conditions. Use them when you need the highest invoice for a client, the smallest expense in a category, or the largest forecast variance for a department.
They are cleaner than combining MAX, MIN, and IF logic manually, especially in operating sheets with categories, owners, months, or statuses.
The syntax
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)- max_range / min_range — the values to evaluate
- criteria_range1 — the first range to check
- criteria1 — the first condition
- [criteria_range2], [criteria2] — optional additional conditions
Step 1. Find the largest expense in one category
Suppose column A contains categories and column B contains amounts. To find the largest software expense:
=MAXIFS(B2:B100, A2:A100, "Software")To find the smallest software expense:
=MINIFS(B2:B100, A2:A100, "Software")This is useful in an expense tracker when one category looks high and you need to find the biggest individual row.
Step 2. Add a status condition
If column C contains approval status, find the largest approved travel expense:
=MAXIFS(B2:B100, A2:A100, "Travel", C2:C100, "Approved")MAXIFS checks every condition before choosing the maximum value.
Step 3. Find the worst budget variance
If column A contains departments, column B contains month names, and column C contains variance, find the largest May variance for Sales:
=MAXIFS(C2:C100, A2:A100, "Sales", B2:B100, "May")For a small business budget, this helps surface the biggest pressure point without manually sorting every budget row.
✦ TIP
Step 4. Use cell references for reusable summaries
If E2 contains the department and F2 contains the month:
=MAXIFS(C2:C100, A2:A100, E2, B2:B100, F2)Now the formula can be copied across a summary table without rewriting the criteria each time.
Common mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Criteria ranges are different sizes | #VALUE! | Keep every range the same height and width |
| Using text month names inconsistently | No match | Use real dates or standardized labels |
| Expecting the row label back | Only the value returns | Pair with XLOOKUP or FILTER to return the row |
| Older Excel version | #NAME? | Use array formulas or AGGREGATE in older workbooks |
The Griddy way
MAXIFS and MINIFS are good at finding the value, but the next question is usually which row caused it and why it changed.
"Find the largest negative budget variance for May, return the line item, and flag whether it is payroll, software, or marketing"
Griddy writes the criteria formula, pulls the matching row context, and turns the result into a reviewable budget exception.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Find the biggest budget exceptions faster
MAXIFS and MINIFS help surface the largest or smallest matching value in budget and expense sheets so review can focus on the rows that changed the plan.
Small Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Small Business Budget for Construction
Plan construction revenue, materials, subcontractors, labor, equipment, insurance, permits, overhead, and operating margin in one budget spreadsheet.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Budget Tracker
Track income, expenses, and savings in one place. Line items, budgeted vs actual totals, and monthly net savings — free to use in your browser.