How to Use MAXIFS and MINIFS in Excel

Introduction

You have a sales table and need the highest revenue from the East region, or the lowest revenue in the West. MAXIFS and MINIFS do exactly that. They follow the same argument pattern as SUMIFS and AVERAGEIFS, but return a max or min instead of a sum or average.

The Dataset

Here's a quarterly sales report we'll use throughout:

	A	B	C	D
3	Rep	Region	Quarter	Revenue
4	Sarah	East	Q1	4200
5	James	West	Q1	3100
6	Sarah	East	Q2	5800
7	Marcus	West	Q2	2700
8	James	West	Q2	4900
9	Marcus	West	Q1	3600

MAXIFS

MAXIFS returns the largest value in a range that meets your conditions. The syntax is:

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

max_range: The range of cells to find the maximum in.
criteria_range1: The first range to evaluate.
criteria1: The condition for the first range.
criteria_range2, criteria2: Additional range-condition pairs.

To find the highest revenue from the East region:

=MAXIFS(D4:D9, B4:B9, "East")

Rep	Region	Revenue	East?
Sarah	East	4200	Yes
James	West	3100	No
Sarah	East	5800	Yes
Marcus	West	2700	No
James	West	4900	No
Marcus	West	3600	No

The East rows have revenues of 4200 and 5800. The result is 5800.

MINIFS

MINIFS works the same way but returns the smallest value. The syntax is identical except for the function name:

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

To find the lowest revenue from the West region:

=MINIFS(D4:D9, B4:B9, "West")

The West rows have revenues of 3100, 2700, 4900, and 3600. The result is 2700.

Here's a full breakdown by region:

Region	MAXIFS	MINIFS
East	5800	4200
West	4900	2700

Multiple Conditions

You can add more criteria pairs, just like SUMIFS. To find the highest West revenue in Q2:

=MAXIFS(D4:D9, B4:B9, "West", C4:C9, "Q2")

Rep	Region	Quarter	Revenue	West AND Q2?
Sarah	East	Q1	4200	No
James	West	Q1	3100	No
Sarah	East	Q2	5800	No
Marcus	West	Q2	2700	Yes
James	West	Q2	4900	Yes
Marcus	West	Q1	3600	No

Marcus (2700) and James (4900) are both West and Q2. The result is 4900.

A full breakdown by region and quarter:

Region	Quarter	Highest Revenue	Lowest Revenue
East	Q1	4200	4200
East	Q2	5800	5800
West	Q1	3600	3100
West	Q2	4900	2700

East has only one rep per quarter in this dataset, so MAXIFS and MINIFS return the same value. West has two reps per quarter, so the results differ.

Using Comparison Operators

Both functions support the same comparison operators as SUMIFS: >, <, >=, <=, <>. To find the highest revenue above 3500:

=MAXIFS(D4:D9, D4:D9, ">3500")

Values above 3500 in the dataset are 4200, 5800, 4900, and 3600. The largest is 5800.

You can combine text criteria with numeric thresholds. To find the lowest West revenue above 3000:

=MINIFS(D4:D9, B4:B9, "West", D4:D9, ">3000")

The West rows above 3000 are 3100, 4900, and 3600. The result is 3100.

When No Rows Match

If no rows meet the conditions, both functions return 0. That can be misleading because 0 looks like a real value.

=MAXIFS(D4:D9, B4:B9, "North")

There is no "North" region in the dataset, so the result is 0, not an error. Wrap the formula in IF to handle this:

=IF(COUNTIFS(B4:B9, "North") = 0, "No data", MAXIFS(D4:D9, B4:B9, "North"))

This checks whether any rows match before running MAXIFS. If none do, it returns "No data" instead of a misleading zero.

Key takeaway: MAXIFS and MINIFS return 0 when no rows match, not an error. If your data could have missing categories, wrap the formula in an IF check to avoid confusion.

Conclusion

MAXIFS finds the largest value that meets your conditions, and MINIFS finds the smallest. They follow the same argument pattern as SUMIFS and AVERAGEIFS, so if you know one, you know them all.

For conditional sums and counts, see our guides on SUMIFS and COUNTIFS. For conditional averages, check out our guide on AVERAGEIF and AVERAGEIFS. For finding the nth largest or smallest value without conditions, see our guide on LARGE, SMALL, and RANK.