How to find the maximum value with criteria in Excel (MAXIFS)
Formula (Excel and Google Sheets)
=MAXIFS(C2:C9,B2:B9,F1)Use MAXIFS with the range to search first, then each condition: =MAXIFS(C2:C9,B2:B9,F1).
- Result on the example
- $3,395 in
F2 - Works in
- Excel 2019 and later, including Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
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.
=MAXIFS(C2:C9,B2:B9,F1)Result3395
C2:C98 rows × 1 col: $1,840, $2,420, $3,395, $1,720, …A range: 8 cells in one column.
B2:B98 rows × 1 col: "West", "East", "West", "North", …A range: 8 cells in one column.
F1"West"The value in F1.
MAXIFS(C2:C9,B2:B9,F1)3395MAXIFS: Largest value that meets every condition.- max_range
C2:C98 rows × 1 col: $1,840, $2,420, $3,395, $1,720, … - criteria_range1
B2:B98 rows × 1 col: "West", "East", "West", "North", … - criteria1
F1"West"
- max_range
In Google Sheets
The same formula works in Google Sheets: =MAXIFS(C2:C9,B2:B9,F1). We ran it in Google Sheets mode on the same data and it returns $3,395.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MAX(FILTER(C2:C9,B2:B9=F1)) | $3,395 | Excel 365 and Google Sheets: FILTER keeps the West sales and MAX picks the largest. |
| =MAX(IF(B2:B9=F1,C2:C9))Excel only | $3,395 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the maximum value with criteria in Excel (MAXIFS)
F2, find the largest sale in the region in F1.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.