The Excel MAXIFS function returns the largest number in a range that meets one or more conditions — a conditional MAX that needs no array formula.
Syntax
| Argument | Description | |
|---|---|---|
max_range | Required | The range of cells to find the maximum from. |
criteria_range1 | Required | The first range to test against criteria1. Must be the same size as max_range. |
criteria1 | Required | The condition that defines which cells in max_range to consider. Can be a number, text, or expression like ">100" or "East". |
criteria_range2, criteria2, ... | Optional | Up to 126 additional range/criteria pairs. All conditions must be met (logical AND). |
How to use it
MAXIFS scans max_range and returns the biggest value, but only across the rows where every criteria range matches its criterion. Each criteria_range must be exactly the same shape as max_range.
Comparison operators go inside the quotes (">100", "<=50", "<>0"), and you can splice in a cell reference with ">"&F1. Wildcards * and ? work for text criteria.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Year | Sale |
| 2 | East | 2024 | 1200 |
| 3 | West | 2024 | 1800 |
| 4 | East | 2025 | 1500 |
| 5 | =MAXIFS(C2:C4,A2:A4,"East") | → 1500 | |
No match? If no rows satisfy the criteria, MAXIFS returns 0 rather than an error — handy, but watch for it skewing results when 0 is also a valid maximum.
Try it: interactive demo
Pick a MAXIFS example to see the formula and its result.
Practice workbook
Frequently asked questions
What does MAXIFS return when nothing matches the criteria?
0, not an error. If your data could legitimately contain 0, test the match count first (e.g. with COUNTIFS) so you don't mistake "no match" for a real maximum of zero.How is MAXIFS different from an array MAX formula?
=MAX(IF(A2:A100="East",C2:C100)) (Ctrl+Shift+Enter). MAXIFS does the same job natively, is easier to read, and supports multiple conditions.Can MAXIFS use more than one condition?
criteria_range, criteria pairs (up to 126). All conditions must be true for a row to count — the logic is AND, not OR.Why do I get a #VALUE! error?
criteria_range must be the same number of rows and columns as max_range. Mismatched range sizes are the most common cause of #VALUE! with MAXIFS.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class