What it does
It is written like SUMIFS: first the range to search for the largest value, then range and condition pairs. =MAXIFS(C2:C50,B2:B50,"West") returns the largest amount among rows whose region is West. MAXIFS is available in Excel 2019 and Microsoft 365.
With a date column it finds the latest date: =MAXIFS(A2:A500,B2:B500,E2) is the last order date of the customer in E2. Remember to give the result a date format, or the cell shows a serial number.
If no row meets the conditions, the result is 0, not an error. In lists where 0 can be a real value, check with COUNTIFS first whether there are any records. For the smallest value use MINIFS.
Syntax
=MAXIFS(max_range, criteria_range1, criteria1, ...)=ÇOKEĞERMAK(mak_aralığı; ölçüt_aralığı1; ölçüt1; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
max_range | Required | The cells to search for the largest value, such as an amount or date column. |
criteria_range1 | Required | The range tested by the first condition; the same size as max_range. |
criteria1 | Required | The first condition: "West", ">=1000", a cell or a joined expression. Further conditions follow as range and criteria pairs. |
Examples
1The highest sale in each region
The regions are in column E. The formula in F2 is filled down and finds the largest amount among each region's rows.
=MAXIFS($C$2:$C$8,$B$2:$B$8,E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Region | Highest | |
| 2 | S-1001 | West | 12,500.00 | West | 12,500.00 | |
| 3 | S-1002 | North | 8,300.00 | North | 15,200.00 | |
| 4 | S-1003 | West | 4,750.50 | South | 9,900.00 | |
| 5 | S-1004 | South | 9,900.00 | |||
| 6 | S-1005 | West | 3,100.00 | |||
| 7 | S-1006 | North | 15,200.00 | |||
| 8 | S-1007 | South | 6,400.00 |
- F2 (filled down to F4)
=MAXIFS($C$2:$C$8,$B$2:$B$8,E2)
The West's highest order is 12,500.00, the North's 15,200.00 and the South's 9,900.00.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Each customer's last order date
This time the range searched for the largest value is the date column. For each customer, the latest date among that customer's rows is found.
=MAXIFS($A$2:$A$7,$B$2:$B$7,E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | Last Order | |
| 2 | 12-Sep-2025 | Brown & Sons | 4200 | Brown & Sons | 9-Oct-2025 | |
| 3 | 2-Oct-2025 | Northgate Ltd | 12500 | Northgate Ltd | 21-Oct-2025 | |
| 4 | 9-Oct-2025 | Brown & Sons | 8300 | Harbour Trade | 30-Sep-2025 | |
| 5 | 27-Aug-2025 | Harbour Trade | 9900 | |||
| 6 | 21-Oct-2025 | Northgate Ltd | 3100 | |||
| 7 | 30-Sep-2025 | Harbour Trade | 15200 |
- F2 (filled down to F4)
=MAXIFS($A$2:$A$7,$B$2:$B$7,E2)
Brown & Sons last ordered on 9-Oct-2025, Northgate Ltd on 21-Oct-2025 and Harbour Trade on 30-Sep-2025.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Highest amount by region and product
Two conditions are given together: region West and product Cable. The largest amount is searched only among rows that meet both.
=MAXIFS(D2:D7,B2:B7,F2,C2:C7,G2)| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Product | Amount | Region | Product | Highest | |
| 2 | 28-Sep-2025 | West | Cable | 4,200.00 | West | Cable | 9,750.50 | |
| 3 | 2-Oct-2025 | West | Socket | 12,500.00 | ||||
| 4 | 6-Oct-2025 | North | Cable | 18,300.00 | ||||
| 5 | 14-Oct-2025 | West | Cable | 9,750.50 | ||||
| 6 | 21-Oct-2025 | South | Cable | 9,900.00 | ||||
| 7 | 30-Oct-2025 | West | Cable | 3,100.00 |
The highest cable sale in the West is 9,750.50. The larger socket sale in the West and cable sale in the North do not meet the conditions, so they are left out.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Result is 0
Cause: No row meets the conditions, and MAXIFS returns 0 in that case.
Fix: Check the spelling of the conditions; to tell 0 from "no records", use
=IF(COUNTIFS(B2:B50,E2)=0,"No records",MAXIFS(C2:C50,B2:B50,E2)).- #VALUE!
Cause: A criteria range is not the same size as max_range.
Fix: Start and end every range on the same rows.
- #NAME?
Cause: The file was opened in Excel 2016 or earlier, which does not know MAXIFS.
Fix: In older versions, enter
=MAX(IF(B2:B50="West",C2:C50))as an array formula with Ctrl+Shift+Enter.
Good to know
- Find a customer's last order date and compare it with today to see how many days they have gone without ordering.
- Put the conditions in cells and fill the formula down; lock the ranges with
$.
In Nexell
Get Nexell to do it from a sentence
Bring your order list into the tool and type this. Nexell builds the summary with real Excel formulas and shows it in the preview first; in the downloaded file, the dates follow the order list.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I find the maximum value with a condition in Excel?
Use =MAXIFS(C2:C50,B2:B50,"West"). For more conditions, add pairs: =MAXIFS(C2:C50,B2:B50,"West",D2:D50,"Cable").
How do I find a customer's last order date?
Give the date column as the range to search: =MAXIFS(A2:A500,B2:B500,"Brown & Sons"). Give the result a date format.