Skip to content

Excel function · Statistics

Excel MAXIFS function

MAXIFS returns the largest value among the rows that meet one or more conditions: the highest sale in the West, a customer's last order date, the highest purchase price of a product.

In Turkish Excel: ÇOKEĞERMAK

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

English Excel=MAXIFS(max_range, criteria_range1, criteria1, ...)
Turkish Excel=Ç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

ArgumentRequiredDescription
max_rangeRequiredThe cells to search for the largest value, such as an amount or date column.
criteria_range1RequiredThe range tested by the first condition; the same size as max_range.
criteria1RequiredThe 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.

F2fx=MAXIFS($C$2:$C$8,$B$2:$B$8,E2)
The highest sale in each region: example table
ABCDEF
1OrderRegionAmountRegionHighest
2S-1001West12,500.00West12,500.00
3S-1002North8,300.00North15,200.00
4S-1003West4,750.50South9,900.00
5S-1004South9,900.00
6S-1005West3,100.00
7S-1006North15,200.00
8S-1007South6,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.

F2fx=MAXIFS($A$2:$A$7,$B$2:$B$7,E2)
Each customer's last order date: example table
ABCDEF
1DateCustomerAmountCustomerLast Order
212-Sep-2025Brown & Sons4200Brown & Sons9-Oct-2025
32-Oct-2025Northgate Ltd12500Northgate Ltd21-Oct-2025
49-Oct-2025Brown & Sons8300Harbour Trade30-Sep-2025
527-Aug-2025Harbour Trade9900
621-Oct-2025Northgate Ltd3100
730-Sep-2025Harbour Trade15200
  • 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.

H2fx=MAXIFS(D2:D7,B2:B7,F2,C2:C7,G2)
Highest amount by region and product: example table
ABCDEFGH
1DateRegionProductAmountRegionProductHighest
228-Sep-2025WestCable4,200.00WestCable9,750.50
32-Oct-2025WestSocket12,500.00
46-Oct-2025NorthCable18,300.00
514-Oct-2025WestCable9,750.50
621-Oct-2025SouthCable9,900.00
730-Oct-2025WestCable3,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

Type this in Nexell

Make a summary table with each customer's last order date

Convert to table

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.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free