MAXIFS
Returns the maximum value among cells specified by a given set of conditions.
=MAXIFS(max_range, criteria_range1, criteria1, ...)This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Returns the maximum value among cells specified by a given set of conditions.
MAXIFS is a statistical function that returns the maximum value among cells specified by one or more sets of conditions or criteria. Introduced in Excel 2019 and Office 365, it replaces the need for complex array formulas like MAX(IF(...)). A critical technical requirement is that the ange and all ange arguments must have identical dimensions and shape; mismatched ranges will trigger a #VALUE! error. The function supports logical operators (>, <, <>, =) and wildcards (*, ?) for text-based filtering. Best practices include using cell references for criteria to maintain dynamic models and ensuring that numerical data in the ange is not stored as text, which the function would ignore. If no cells meet the criteria, MAXIFS returns 0, which can be ambiguous if 0 is also a valid data point in your range.
Quick reference
Syntax
=MAXIFS(max_range, criteria_range1, criteria1, ...)Inputs
Arguments
Example: C2:C100
Example: A2:A100
Example: "Class A"
Formula patterns
Examples
Highest sales in a specific region
=MAXIFS(C2:C10, A2:A10, "North")Maximum score with multiple criteria
=MAXIFS(D2:D20, B2:B20, ">80", C2:C20, "A")Finding latest date for a specific task ID
=MAXIFS(B2:B50, A2:A50, E2)Avoid these issues
Common Errors
#VALUE!
Cause: The size and shape of the max_range and criteria_range arguments are not the same.
Fix: Ensure all range references (e.g., A2:A100 and B2:B100) have the exact same starting and ending row/column numbers.
Returns 0 unexpectedly
Cause: No cells meet the criteria, or the cells meeting the criteria are empty or contain non-numeric data.
Fix: Check your criteria for typos and verify that the max_range contains actual numbers rather than numbers formatted as text.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Returns the maximum value among cells specified by a given set of conditions.
ange: Range to find max in ange1: Range to check criteria1: Condition
#VALUE!: Ensure all range references (e.g., A2:A100 and B2:B100) have the exact same starting and ending row/column numbers. Returns 0 unexpectedly: Check your criteria for typos and verify that the ange contains actual numbers rather than numbers formatted as text.