MAXIFS and MINIFS in Excel: Max If With Criteria

In short: =MAXIFS(max_range,criteria_range1,criteria1,…) and =MINIFS(min_range,criteria_range1,criteria1,…). The numbers come first, followed by pairs of range and condition, exactly like SUMIFS.

Try it now

The sheet shows sales by rep across three regions. Find the top figure in the North region. A plain MAX would grab the highest value from the South, so you need a condition.

Task In E2, find the highest sales figure in the North region.

	A	B	C	E
1	Region	Sales rep	Sales $	Top sales, North
2	North	Jana Weber	4200
3	South	Murat Kaya	5100
4	North	Lena Hoffmann	6800
5	West	Tom Schulz	3900
6	South	Sara Klein	7300
7	North	Ali Demir	5900
8	West	Mia Wagner	6100

The MAXIFS and MINIFS syntax

Argument	Meaning	In the example
max_range or min_range	The cells the result is taken from	`C2:C8` (the sales)
criteria_range1	The range that is tested	`A2:A8` (the regions)
criteria1	The condition for that range	`"North"`
criteria_range2, criteria2, …	Optional extra pairs, all of which must match	for example `B2:B8,"Online"`

=MAXIFS(C2:C8,A2:A8,"North") considers only the North rows and returns the largest sales value among them. To add a second condition, such as the year 2026 in column D, append another pair: =MAXIFS(C2:C8,A2:A8,"North",D2:D8,2026). As with AVERAGEIFS, the conditions are joined with AND.

One more: the cheapest item in a category

MINIFS works the same way in the other direction. This time you want the lowest price in the Filing category. Careful: the cheapest item overall belongs to a different category.

Task In E2, find the lowest price in the Filing category.

	A	B	C	E
1	Item	Category	Price $	Lowest price, Filing
2	Lever arch file	Filing	3.49
3	Ballpoint pen	Writing	1.29
4	Hanging folder	Filing	0.89
5	Highlighter	Writing	0.79
6	Sheet protectors, pack of 100	Filing	4.99
7	Fineliner	Writing	1.49
8	Magazine file	Filing	2.79

The answer is =MINIFS(C2:C8,B2:B8,"Filing"). You can point the criteria at a cell instead of typing the text. With the category in E1, use =MINIFS(C2:C8,B2:B8,E1), then list every category down column E for a quick summary table.

Latest date per customer

One of the most useful tricks with MAXIFS: when did a customer last order? Excel stores dates as serial numbers, so the latest date is simply the largest number, and MAXIFS finds it. MINIFS gives you the first order.

Task In E2, find the date of the most recent order from Hansa Logistik.

	A	B	C	E
1	Customer	Order date	Amount $	Last order, Hansa
2	Hansa Logistik	2/18/2026	1840
3	Weber Druck	1/12/2026	620
4	Hansa Logistik	4/3/2026	2310
5	Becker Bau	1/26/2026	980
6	Weber Druck	5/18/2026	1150
7	Hansa Logistik	3/11/2026	760
8	Becker Bau	4/21/2026	1420

The only catch is formatting. Without a date format the result cell shows a serial number such as 46115. Our date functions lesson covers how date values work.

Common MAXIFS and MINIFS problems

A result of 0 when there is no 0 in the data

If no row matches, MAXIFS and MINIFS return 0 rather than an error. With MINIFS that is easy to miss, because 0 looks like a genuine minimum. Check the criteria for typos and trailing spaces, or use COUNTIFS to confirm that any rows match at all.

#NAME? in older versions

Versions without these functions show #NAME?. That also happens when a colleague opens your file in Excel 2016 without a Microsoft 365 subscription. The fallback is described below.

Ranges of different sizes

The max range and every criteria range must be the same size. =MAXIFS(C2:C8,A2:A9,"North") returns #VALUE!.

Writing it like SUMIF

=MAXIFS(A2:A8,"North",C2:C8) follows the SUMIF pattern. MAXIFS has no short form like that: the numbers always come first.

MAXIFS and MINIFS in German Excel

German Excel calls them MAXWENNS and MINWENNS, with semicolons as separators:

English	German
`=MAXIFS(C2:C8,A2:A8,"North")`	`=MAXWENNS(C2:C8;A2:A8;"Nord")`
`=MINIFS(C2:C8,B2:B8,"Filing")`	`=MINWENNS(C2:C8;B2:B8;"Ablage")`
AGGREGATE	AGGREGAT

Our exercises accept both spellings. Pick your Excel language below each exercise.

Which Excel versions have MAXIFS?

MAXIFS and MINIFS are available in Microsoft 365, Excel 2024, Excel 2021 and Excel 2019 on Windows and Mac. Excel 2016 and earlier do not have them unless you use a Microsoft 365 subscription.

On an older version there are two workarounds. The first is MAX with IF as an array formula: =MAX(IF(A2:A8="North",C2:C8)). In older versions you confirm it with Ctrl+Shift+Enter, otherwise it returns a wrong value or #VALUE!. Swap MAX for MIN to get the lowest value. The second is AGGREGATE, available from Excel 2010, which needs no special key combination: =AGGREGATE(14,6,C2:C8/(A2:A8="North"),1). Function 14 is LARGE and option 6 ignores error values. The division creates #DIV/0! for every other region, and those rows drop out. Use 15 (SMALL) instead of 14 for the minimum. If you have MAXIFS, though, it is far easier to read.

FAQ

How do I return the name that goes with the conditional max?

Get the value with MAXIFS first, then look up the row where region and value both match, for example =XLOOKUP(1,(A2:A8="North")*(C2:C8=F1),B2:B8) with the MAXIFS result in F1.

What if I need the average instead of the max?

AVERAGEIFS uses exactly the same layout. Without any conditions, plain AVERAGE, MIN and MAX are enough.

How do I get the second highest value with a condition?

MAXIFS only returns the top value. For second place use LARGE with a condition, such as =AGGREGATE(14,6,C2:C8/(A2:A8="North"),2). LARGE and rankings are the next lesson.

Do comparisons and wildcards work?

Yes, the same as in SUMIFS. ">=2026" tests for numbers from 2026 upwards and "North*" matches anything that starts with North.

Learn this next: LARGE, SMALL and RANK

MAX finds the top value. LARGE also gives you second and third place, and RANK.EQ shows where every value sits in the list.

Practice LARGE and RANK

MAXIFS and MINIFS: the max or min with conditions