MAXIFS function
MAXIFS returns the largest value among the rows that meet one or more conditions: the biggest deal in a region, the latest order date for a customer. It follows the SUMIFS layout, with the range to search first. When nothing matches it returns 0 rather than an error, so check for that.
- Available in
- Excel 2019 and later, including Microsoft 365
- Returns
- The largest matching value, or 0 if no row meets the conditions.
- Google Sheets
- Also available in Google Sheets.
Syntax
=MAXIFS(max_range, criteria_range1, criteria1, ...)
| Argument | Required? | What it is |
|---|---|---|
max_range | Required | The cells to find the largest value in. |
criteria_range1 | Required | The first range to check. |
criteria1 | Required | The condition for the first range. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | =MAXIFS(C2:C8,B2:B8,"East") | $31,000 | The biggest deal in the East region |
| G3 | =MAXIFS(C2:C8,B2:B8,"East",D2:D8,"Closed") | $26,200 | Add a second condition to skip open deals |
| G4 | =MAXIFS(C2:C8,B2:B8,"South") | $0 | No South deals: MAXIFS returns 0, not an error |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Deal | Region | Value | Stage | Check | Result | |
| D-01 | East | $18,500 | Closed | Largest East deal | $31,000 | |
| D-02 | West | $22,400 | Open | Largest closed East deal | $26,200 | |
| D-03 | East | $31,000 | Open | Largest South deal | $0 | |
| D-04 | East | $12,750 | Closed | |||
| D-05 | North | $9,800 | Closed | |||
| D-06 | West | $15,600 | Closed | |||
| D-07 | East | $26,200 | Closed |
Practice MAXIFS
Graded instantly, then re-checked on changed data.
Last order date per customer
F2, return the most recent order date for the customer in E2. Fill down to F4.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| A 0 that should be "no data" | MAXIFS returns 0 when nothing matches. Check with COUNTIFS first, or use IF(COUNTIFS(...)=0,"",MAXIFS(...)). |
| #NAME? | Your version is older than Excel 2019. Use =MAX(IF(B2:B8="East",C2:C8)) instead, confirmed with Ctrl+Shift+Enter in old versions. |
| #VALUE! | max_range and the criteria ranges are different sizes. |
Tips
- MAXIFS on a date column returns the most recent matching date, like a customer's last order.
Questions
How do I find the max value with a condition in Excel?
Use =MAXIFS(C2:C8,B2:B8,"East") in Excel 2019 or later.
Why does MAXIFS return 0?
No rows met the conditions. MAXIFS returns 0 instead of an error in that case.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.