# How to find the maximum value with criteria in Excel (MAXIFS)

Use MAXIFS with the range to search first, then each condition: `=MAXIFS(C2:C9,B2:B9,F1)`.

**Excel:** `=MAXIFS(C2:C9,B2:B9,F1)` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Region | Sale |  | Region | West |
| 2026-01-05 | West | 1840 |  | Largest sale |  |
| 2026-01-11 | East | 2420 |  |  |  |
| 2026-01-19 | West | 3395 |  |  |  |
| 2026-02-02 | North | 1720 |  |  |  |
| 2026-02-09 | West | 2210 |  |  |  |
| 2026-02-16 | East | 1955 |  |  |  |
| 2026-02-23 | West | 2610 |  |  |  |
| 2026-03-03 | South | 3980 |  |  |  |

Formula in F2: `=MAXIFS(C2:C9,B2:B9,F1)`

Result: $3,395

## How it works

MAXIFS looks only at the rows where every condition holds, here region = West, and returns the largest value from `C2:C9` among them: 3,395. The South sale of 3,980 is bigger but isn't in the West. Add more range-and-condition pairs as needed, like SUMIFS.

## Other ways

- `=MAX(FILTER(C2:C9,B2:B9=F1))`: Excel 365 and Google Sheets: FILTER keeps the West sales and MAX picks the largest.
- `=MAX(IF(B2:B9=F1,C2:C9))`: For Excel 2016 and older, entered with Ctrl+Shift+Enter.

## Common mistakes

- MAXIFS returns 0 when no row matches, not an error, which can look like a real result.
- MAXIFS needs Excel 2019 or later. In older versions it shows #NAME?; use the MAX(IF()) version.

## Questions

**How do I find the latest date for a customer?**

Dates are numbers, so MAXIFS works: `=MAXIFS(A2:A9,B2:B9,F1)` returns the most recent West sale date.

**How do I return who or when, not just the value?**

Look up the max: `=XLOOKUP(1,(B2:B9=F1)*(C2:C9=F2),A2:A9)` returns the date of the largest West sale.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/maxifs-maximum-with-criteria