How to Use MAXIFS and MINIFS in Excel

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

• 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:

To find the smallest software expense:

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 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:

For a small business budget, this helps surface the biggest pressure point without manually sorting every budget row.

Step 4. Use cell references for reusable summaries

If E2 contains the department and F2 contains the month:

Now the formula can be copied across a summary table without rewriting the criteria each time.

Common mistakes

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.

Return the row behind the result

MAXIFS and MINIFS return a number, not the record that produced it. If a reviewer needs the invoice, owner, or date behind the number, use the result as a second lookup condition. For example, calculate the largest amount for a client, then use XLOOKUP or FILTER to return rows matching that amount and the same client. This matters when duplicate amounts are possible: a single lookup may return only the first row, while FILTER can show every tie for review.

Before trusting the result, verify that all criteria ranges have the same height as the max or min range. Also decide how blanks and zeros should be treated. A zero may be a real measured value, while a blank may mean the source was not supplied. Document that choice next to the summary so the number is not mistaken for a complete ranking.

Sources

• Microsoft Support: MAXIFS function
• Microsoft Support: MINIFS function