MAXIFS and MINIFS: Conditional Maximums and Minimums (Multiple Criteria, Ties, and Related Values)

MAXIFS and MINIFS: Conditional Maximums and Minimums (Multiple Criteria, Ties, and Related Values)

MAXIFS and MINIFS: Conditional Maximums and Minimums (Multiple Criteria, Ties, and Related Values)

“What is the highest sales amount for the Seoul Keyboard category?” “What is the lowest unit price for that month?” — Use MAXIFS / MINIFS to find the answer cleanly, then retrieve the row’s details at the same time.

Basic Syntax

FunctionSyntaxDescription
MAXIFS=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Returns the maximum among values that meet the criteria
MINIFS=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Returns the minimum among values that meet the criteria

TIP: All ranges must be the same size. For date and number comparisons, join an operator string and a value with &, as in ">="&cell.

8 Practical Examples

1) Maximum sales for Region = Seoul

=MAXIFS(Sales[Amount], Sales[Region], "Seoul")

2) Minimum unit price for Category = Keyboard

=MINIFS(Price, Category, "Keyboard")

3) Maximum sales for a date range (current month)

=MAXIFS(C:C, B:B, ">="&EOMONTH(F2,-1)+1, B:B, "<="&EOMONTH(F2,0))

4) Maximum amount where text contains “Pro”

=MAXIFS(C2:C100, A2:A100, "*Pro*")

5) Minimum lead time with multiple criteria (Region = Seoul AND Status = Delivered)

=MINIFS(LeadTime, Region, "Seoul", Status, "Delivered")

6) Minimum value excluding 0 and blanks

=MINIFS(C2:C100, C2:C100, "<>", C2:C100, "<>0")

7) List all rows tied for the maximum value (Microsoft 365)

=LET(mx, MAXIFS(C:C, B:B, "Seoul"),
  FILTER(A:C, (B:B="Seoul")*(C:C=mx)))

8) Top N values (pseudo-LARGEIFS) — Microsoft 365

=TAKE(SORT(FILTER(C2:C100, B2:B100="Seoul"),,-1), N)

Retrieve Other Information from the Extreme-Value Row

An extreme value alone is not always enough. Use XLOOKUP/INDEX as shown below to retrieve the product name, order number, or other information from the same row.

Product name for the maximum value (first matching row)

=LET(mx, MAXIFS(Sales[Amount], Sales[Region], "Seoul"),
 XLOOKUP(mx, Sales[Amount], Sales[Item], , 0))

Order number for the minimum lead time (including criteria)

=LET(mn, MINIFS(LeadTime, Region,"Seoul", Status,"Delivered"),
 XLOOKUP(1, (LeadTime=mn)*(Region="Seoul")*(Status="Delivered"), OrderID))

Use FILTER when you need all tied rows.

Alternatives for Older Versions

In versions without MAXIFS or MINIFS, use array formulas or AGGREGATE.

MAXIFS alternative

=MAX(IF(Region="Seoul", Amount))     // Ctrl+Shift+Enter

MINIFS alternative with multiple criteria

=MIN(IF((Region="Seoul")*(Category="Keyboard"), Price))

Ignore errors and blanks with AGGREGATE

=AGGREGATE(14, 6, Amount/(Region="Seoul"), 1)   // 14=Large, 6=Ignore errors

Mistakes, Performance, and Accuracy Checks

  • Mismatched range sizes → Keep the number of rows and columns the same in every range.
  • Numbers stored as text → Convert with VALUE or standardize the source format.
  • Handling ties → Decide whether you need only the value or a list of rows (XLOOKUP vs. FILTER).
  • Overusing full-column references → In large files, use tables or precise ranges for better speed.
  • Clean criteria first → Remove noise with TRIM, CLEAN, and SUBSTITUTE before comparing to improve accuracy.

Summary

GoalTypical Formula
Conditional maximumMAXIFS(value_range, criteria_range, criteria...)
Conditional minimumMINIFS(value_range, criteria_range, criteria...)
Details from the extreme-value rowXLOOKUP(extreme_value, value_range, return_range)
Older versionsMAX/MIN(IF(criteria,value)) array formula

FAQ

How are blank cells handled?

MAXIFS and MINIFS ignore blank cells by default. To exclude 0, add "<>0" as a criterion.

Are text criteria case-sensitive?

No. If you need case-sensitive matching, use EXACT with FILTER or an array formula.

Try the formulas above with “maximum sales where Region = Seoul” or “minimum lead time where Status = Delivered” in your data. Connecting the extreme value with the matching row details can significantly speed up your analysis.

Leave a Reply

Your email address will not be published. Required fields are marked *