MAXIFS function
Quick answer
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.
- Syntax
=MAXIFS(max_range, criteria_range1, criteria1, ...)- Most common use
=MAXIFS(C2:C8,B2:B8,"East")returns $31,000 (The biggest deal in the East region)
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.
Lessons that use MAXIFS
Answers that use MAXIFS
In other languages
MAXIFS is MAX.SI.CONJUNTO in Spanish Excel and MAXWENNS in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .