MAX and MIN return the highest or lowest value in a range — but they ignore everything else. They cannot filter by region, date, product, or any other condition. The MAXIFS function solves this by returning the largest value that meets one or more criteria. Similarly, the MINIFS function returns the smallest qualifying value. Together, they let you ask questions like "What was the highest single sale in the East region this quarter?" without sorting or filtering the data first.
Both functions belong to the same *IFS family as COUNTIFS, SUMIFS, and AVERAGEIFS. Consequently, their syntax follows an identical pattern — the value range comes first, followed by one or more range-criteria pairs.
What Is the Syntax for MAXIFS and MINIFS?
Both functions share the same structure. Only the direction of the result differs.
| Argument | Required? | What it does |
|---|---|---|
| max_range / min_range | Required | The range containing the numbers to evaluate. Text, blanks, and logical values are ignored. Only rows where all conditions are TRUE contribute to the result. |
| criteria_range1 | Required | The first range to test. Must be the same size and shape as max_range / min_range. |
| criteria1 | Required | The condition for criteria_range1. Supports text, numbers, operators (>, <, <>, =), wildcards (* and ?), and cell references joined with &. |
| criteria_range2, criteria2… | Optional | Additional range-criteria pairs. Each adds another AND condition. Up to 126 pairs are supported. |
=IF(MAXIFS(...)=0, "No match", MAXIFS(...)) — or use COUNTIFS first to check whether any matching rows exist.
How Do MAXIFS and MINIFS Differ from MAX and MIN?
MAX and MIN have no conditional logic. They evaluate every number in the range. MAXIFS and MINIFS, by contrast, evaluate only the rows where every condition is true. The table below summarises the key differences.
| Feature | MAXIFS / MINIFS | MAX / MIN |
|---|---|---|
| Conditional filtering | ✅ Up to 126 conditions | ❌ None — evaluates all rows |
| Supports operators (>, <, <>) | ✅ Yes | ❌ No |
| Supports wildcards | ✅ Yes (* and ?) | ❌ No |
| Result when no match | Returns 0 | Returns #VALUE! on empty range |
| Available in Excel 2016? | ❌ No — use MAX(IF()) instead | ✅ Yes — all versions |
Example 1: Find the Highest and Lowest Sale in a Region
The most common use is finding the peak or floor value within a specific category. MAXIFS returns the best sale for a region. MINIFS returns the worst. Neither requires sorting or filtering the source data.
Example 2: Two Conditions — Region and Product
Adding a second range-criteria pair narrows the result further. Only rows where both conditions are true contribute to the maximum or minimum. Additional pairs tighten the filter even more — each one adds an AND condition.
Example 3: Find Max and Min Within a Date Range
Date criteria work exactly as they do in SUMIFS and AVERAGEIFS. Two conditions on the same date column create a window. Dynamic date expressions using TODAY() keep the window current without any manual editing.
Example 4: Numeric Bounds — Exclude Outliers
Sometimes you want the highest or lowest value within a specific numeric band — excluding zeros, capping at a threshold, or filtering out extreme values. MAXIFS and MINIFS support comparison operators on the same column as the value range, making this straightforward.
Example 5: Build a Max/Min Summary Table
MAXIFS and MINIFS are ideal for populating summary grids. Place region names in one axis and product names in another. One formula with mixed references then fills the entire grid automatically when copied across and down.
Using Mixed References for the Grid
Lock the value range and criteria ranges fully with $. Use a mixed reference for the row-header criteria — column locked, row free. Similarly, use a mixed reference for the column-header criteria — row locked, column free. As a result, each cell in the grid evaluates the correct combination automatically.
Example 6: Fallback for Excel 2016 — MAX(IF) and MIN(IF)
MAXIFS and MINIFS are not available in Excel 2016 or earlier. For compatibility with older versions, use MAX(IF()) or MIN(IF()) array formulas. In Excel 365 and Excel 2019+, both approaches produce identical results — so MAXIFS is simply the cleaner choice for new files.
When to Use MAX(IF) Instead
Use MAX(IF) specifically when the file will be shared with colleagues who may open it in Excel 2016 or Excel 2013. Otherwise, MAXIFS is always preferable — it is shorter, does not require Ctrl+Shift+Enter, and is easier to extend with additional criteria.
How to Fix Common MAXIFS and MINIFS Issues
Result is 0 but you expect a number
MAXIFS returns 0 when no rows match all the criteria. This is different from AVERAGEIFS, which returns a #DIV/0! error. Check the criteria spellings, verify the range sizes all match, and confirm that matching rows exist. Additionally, use COUNTIFS with the same criteria to audit: if it returns 0, no rows match and the 0 result from MAXIFS is correct.
#VALUE! error
A #VALUE! error means at least one criteria_range is a different size or shape from max_range. Every range must have the same row count and column count. For example, if max_range is B2:B100, then every criteria_range must also span rows 2 to 100. Check each range reference individually.
MAXIFS returns 0 but there are matching rows with positive values
The value range may contain numbers stored as text. MAXIFS ignores text values, so it finds no numeric values to compare and returns 0. Verify with ISNUMBER, and convert text-numbers using VALUE() or the Data → Text to Columns tool. Additionally, check whether leading or trailing spaces in the criteria column are causing the match to fail silently.
Frequently Asked Questions
-
What is the difference between MAX and MAXIFS?MAX returns the largest value across an entire range with no filtering. MAXIFS, by contrast, returns the largest value only among the rows that meet all specified conditions. For example, MAX(B2:B100) finds the overall highest sale. MAXIFS(B2:B100, A2:A100, "East") finds the highest sale only in the East region. Consequently, MAXIFS is far more useful for segmented data analysis.
-
Why does MAXIFS return 0 instead of an error when nothing matches?This is by design. MAXIFS returns 0 when no rows meet all the criteria, because 0 is the numeric identity element for a maximum operation — it means "no qualifying value found above zero". This differs from AVERAGEIFS, which returns #DIV/0! on no match. To distinguish a genuine zero result from a no-match result, use COUNTIFS first: if COUNTIFS returns 0, then MAXIFS returning 0 means no matching rows exist rather than a real zero value.
-
How do I use MAXIFS with dates?Use comparison operators joined with the DATE function for reliable date criteria: =MAXIFS(B2:B100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<="&DATE(2026,3,31)). Hardcoding dates as text strings like "1/1/2026" can cause issues because regional date formats vary. Using DATE() guarantees the correct numeric date value regardless of locale settings.
More Questions About MAXIFS and MINIFS
-
What is the fallback formula for Excel 2016?Use MAX(IF()) as an array formula: =MAX(IF(A2:A100="East", B2:B100)). In Excel 2016 and earlier, confirm with Ctrl+Shift+Enter instead of Enter. For multiple conditions, multiply the Boolean arrays together: =MAX(IF((A2:A100="East")*(B2:B100="Widget"), C2:C100)). In Excel 365 and Excel 2021, these array formulas work as normal formulas without Ctrl+Shift+Enter.
-
Can MAXIFS and MINIFS use wildcards?Yes. Both functions support the asterisk (*) and question mark (?) wildcards in text criteria. An asterisk matches any sequence of characters — for example, "East*" matches "East London" and "East Midlands". A question mark matches exactly one character. To search for a literal asterisk or question mark in the data, prefix it with a tilde: "~*" or "~?". Wildcards are not case-sensitive.
-
Which Excel versions support MAXIFS and MINIFS?MAXIFS and MINIFS are available in Excel 2019, Excel 2021, Excel 2024, and Microsoft 365 on both Windows and Mac. They are also supported in Excel for the web. Notably, they are not available in Excel 2016 or earlier — those versions display a #NAME? error. For backward-compatible files, use MAX(IF()) and MIN(IF()) array formulas as described in Example 6.