# MAXIFS function in Excel

> 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.

Source: https://spreadsheetpractice.com/functions/maxifs · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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)
- **Available in:** Excel 2019 and later, including Microsoft 365
- **Category:** [Conditional sum and count functions](https://spreadsheetpractice.com/functions/category/conditional)
- **Returns:** The largest matching value, or 0 if no row meets the conditions.
- **Google Sheets:** Also available in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Deal | Region | Value | Stage |  | Check | Result |
| 2 | D-01 | East | $18,500 | Closed |  | Largest East deal | $31,000 |
| 3 | D-02 | West | $22,400 | Open |  | Largest closed East deal | $26,200 |
| 4 | D-03 | East | $31,000 | Open |  | Largest South deal | $0 |
| 5 | D-04 | East | $12,750 | Closed |  |  |  |
| 6 | D-05 | North | $9,800 | Closed |  |  |  |
| 7 | D-06 | West | $15,600 | Closed |  |  |  |
| 8 | D-07 | East | $26,200 | Closed |  |  |  |

## 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.

## Related functions

- [MINIFS](https://spreadsheetpractice.com/functions/minifs): MINIFS returns the smallest value among the rows that meet one or more conditions: the cheapest quote for a part, the earliest due date in a project, the lowest score above zero.
- [MAX](https://spreadsheetpractice.com/functions/max): MAX returns the largest number from a list of values or ranges: the top sale, the latest date, the highest temperature.
- [SUMIFS](https://spreadsheetpractice.com/functions/sumifs): SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer.

## 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.

## Practice

A graded MAXIFS exercise ("Last order date per customer") is on [the MAXIFS page](https://spreadsheetpractice.com/functions/maxifs). It is checked instantly, then re-checked on changed data.

Lessons that use MAXIFS:

- [Lesson 38: MAXIFS & MINIFS by group](https://spreadsheetpractice.com/learn/maxifs-minifs-by-group) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
