MAX, MIN, MAXIFS and MINIFS functions in Excel

MAX and MIN return the largest and smallest numbers in a range; MAXIFS and MINIFS do the same for the rows that meet conditions.

MAX and MIN find the extremes: the top mark, the lowest price, the latest date, the earliest order. They are simple to use, and a few patterns built on them are worth knowing.

Syntax

=MAX(number1, [number2], ...)

=MIN(number1, [number2], ...)

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=MINIFS(min_range, criteria_range1, criteria1, ...)

Argument	What it is
`number1, number2, ...`	Numbers, cells or ranges.
`max_range, min_range`	The cells to take the largest or smallest value from.
`criteria_range1, criteria1`	A range to test and its condition, as in SUMIFS.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=MAX(C2:C9)
Result: 92
The highest Maths mark.
=MIN(C2:C9)
Result: 33
The lowest.
=MAX(C2:C9)-MIN(C2:C9)
Result: 59
The range: the gap between highest and lowest.
=MAXIFS(C2:C9,B2:B9,"8B")
Result: 88
The highest Maths mark in class 8B only.
=MINIFS(E2:E9,B2:B9,"8A")
Result: 58
The lowest English mark in class 8A.
=INDEX(A2:A9,MATCH(MAX(C2:C9),C2:C9,0))
Result: Meena
Who has the highest Maths mark? MAX finds the mark, MATCH finds its row, INDEX returns the name.
=MAX(C2:E2)
Result: 91
Across a row: Asha's best mark of the three.
=MIN(100,C2*1.5)
Result: 100
A cap: one and a half times the mark, but never more than 100.

How they work

Both look through every number you give them. Text, empty cells and TRUE or FALSE inside a range are ignored. If a range has no numbers at all, the result is 0, which can be misleading: a MAX of 0 may mean "no data", not "the largest is zero".

Dates are numbers, so =MAX(A2:A13) on a column of dates returns the latest date and MIN the earliest. Format the result cell as a date.

Whose value is it?

MAX tells you the highest mark, not who scored it. To get the name, find the position of the maximum and read the name from the same position, as the sixth example does:

=INDEX(names, MATCH(MAX(marks), marks, 0))

In Excel 2021 and Microsoft 365, XLOOKUP is shorter: =XLOOKUP(MAX(C2:C9),C2:C9,A2:A9). If two students share the top mark, both formulas return the first. FILTER lists them all.

Conditions: MAXIFS and MINIFS

MAXIFS and MINIFS return the largest or smallest value among the rows that pass one or more conditions: the best mark in one class, the cheapest item in one category, the latest order of one customer. Their arguments follow the pattern of SUMIFS: the range to take the answer from comes first, then pairs of range and condition.

They are available in Excel 2019, Excel 2021 and Microsoft 365. When no row matches, they return 0.

Limits and floors

MAX and MIN are a tidy way to stop a calculated value going out of bounds.

A ceiling: =MIN(100, value) never exceeds 100.
A floor: =MAX(0, value) never goes below zero. It is the neat way to write "profit, or nothing if it is a loss".
Both: =MAX(0, MIN(100, value)) keeps a value between 0 and 100.

People often write these with IF. =MAX(0,A1-B1) says the same as =IF(A1-B1<0,0,A1-B1) in half the space, and names the cells once.

The smallest value that is not zero

Zeros often stand for "nothing entered", and MIN then returns 0. To skip them: =MINIFS(C2:C9,C2:C9,">0").

Second highest, third lowest

MAX and MIN give only the very top and bottom. For the second, third or any other place, use LARGE and SMALL. For a position in the order, use RANK.

Common mistakes

An error anywhere in the range makes MAX and MIN return that error.
Numbers stored as text are ignored, so the true maximum may be missed.
Reading a 0 from MAXIFS as a real value when it means no row matched.
#NAME? for MAXIFS and MINIFS in Excel 2016 and earlier.

In Google Sheets

MAX, MIN, MAXIFS and MINIFS are all available in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.