Find Conditional Maximum and Minimum Values with Excel MAXIFS and MINIFS

There are many times when you need to find the “largest value / smallest value” based on criteria. For example: “What was the highest sales amount for shirts sold through the online channel in Seoul?” or “What was the lowest discounted price for Brand A?”

This article covers how to use the MAXIFS and MINIFS functions, from the basics and practical examples to formulas for older versions and error troubleshooting. By the end, you will be able to create a conditional maximum/minimum value template that you can apply immediately to sales, inventory, or HR data.

Quick Fix – Find Conditional Maximum and Minimum Values in 3 Minutes

Step 1: Check the Data Structure

For example, assume you have sales data like this:

What we want to know: “The largest and smallest sales amounts among rows that meet the criteria Seoul + Online + Shirt.”

Step 2: Find a Conditional Maximum with MAXIFS

Select the cell where you want to display the result (for example, H2) and enter the following formula:

=MAXIFS(E2:E100, B2:B100, "셔츠", C2:C100, "서울", D2:D100, "온라인")

It selects only rows in E2:E100 where the product is “Shirt,” the region is “Seoul,” and the channel is “Online,” then returns the largest sales amount.

Step 3: Find a Conditional Minimum with MINIFS

In the adjacent cell (for example, H3), enter the formula for the minimum value:

=MINIFS(E2:E100, B2:B100, "셔츠", C2:C100, "서울", D2:D100, "온라인")

This finds the smallest sales amount under the same conditions.

MAX / MIN vs. MAXIFS / MINIFS

MAX / MIN vs. MAXIFS / MINIFS

MAX and MIN simply find the largest or smallest value in a range and do not use criteria. In contrast, the MAXIFS and MINIFS functions select only cells that meet one or more criteria, then find the maximum and minimum values among them. Think of them as related functions in the *IFS family, along with COUNTIFS, SUMIFS, and AVERAGEIFS.

Function Syntax and How It Works

The function syntax is as follows:

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

• max_range / min_range: The numeric range in which to find the maximum/minimum value
• criteria_range1, criteria1: The first criteria range and criterion
• [criteria_range2, criteria2]: Where you add second and third criteria (up to 126 pairs)

Supported Versions and Usage Notes

MAXIFS and MINIFS are supported in Excel 2019 and later, Microsoft 365, and Excel for the web. They may not be supported in older versions such as Excel 2016 and earlier. In that case, use MAX(IF()) or MIN(IF()) array formulas, or the AGGREGATE function instead.

Practical Example 1 – Find Maximum and Minimum Sales by Product and Region

Create Sample Sales Data

Enter the following sample data into a worksheet:

Find Maximum/Minimum Sales with One Criterion

Goal: “Find the largest and smallest sales amounts among all sales in Seoul.”

Maximum:

=MAXIFS(E2:E11, C2:C11, "서울")

Minimum:

=MINIFS(E2:E11, C2:C11, "서울")

Find Maximum/Minimum Sales with Multiple Criteria (Product + Region + Channel)

Goal: Find the maximum/minimum sales amount among rows that meet all these criteria: Product = Shirt, Region = Seoul, Channel = Online.

=MAXIFS(E2:E11, B2:B11, "셔츠", C2:C11, "서울", D2:D11, "온라인")

=MINIFS(E2:E11, B2:B11, "셔츠", C2:C11, "서울", D2:D11, "온라인")

Practical Example 2 – Advanced Use with Date Ranges and Numeric Criteria

Find the Highest Sales Amount During a Specific Period

Assume that the start date and end date are entered in cells G2 and G3, and the product name is entered in G4.

=MAXIFS(E2:E11,
        B2:B11, $G$4,
        A2:A11, ">="&$G$2,
        A2:A11, "<="&$G$3)

You can find the minimum value in the same way by using MINIFS.

Use with “At Least/At Most” Numeric Criteria

This example finds the maximum/minimum sales amount only for rows where the region is Seoul and the sales amount is 60,000 or more.

=MAXIFS(E2:E11, C2:C11, "서울", E2:E11, ">=60000")

=MINIFS(E2:E11, C2:C11, "서울", E2:E11, ">=60000")

Alternative Formulas for Versions Without MAXIFS and MINIFS (2016 and Earlier)

Use MAX(IF()) and MIN(IF()) Array Formulas

This is an array formula example for finding the maximum sales amount when Product = Shirt and Region = Seoul.

=MAX(IF((B2:B11="셔츠")*(C2:C11="서울"), E2:E11))

For the minimum value, replace MAX with MIN.

=MIN(IF((B2:B11="셔츠")*(C2:C11="서울"), E2:E11))

Use the AGGREGATE Function as an Alternative

=AGGREGATE(14, 6, E2:E11/((B2:B11="셔츠")*(C2:C11="서울")), 1)

Strategies to Choose by Version

• Templates used across multiple versions: MAX(IF()) and MIN(IF()) array formulas
• Microsoft 365 / current versions: Use MAXIFS and MINIFS whenever possible
• Large datasets or complex criteria: Use alongside PivotTables and Power Query

Common Errors and Troubleshooting

Why Does the Result Only Return 0?

• The criteria and actual data differ slightly because of spaces, hidden characters, or similar issues.
• The max_range and criteria_range have different numbers of rows.
• There are no values that actually meet the criteria.

When Errors Such as #VALUE! or #N/A Occur

• The ranges are different sizes.
• Text is mixed into a numeric range.
• Dates were entered as text and are not recognized as dates.

MAXIFS / MINIFS Troubleshooting Table

Patterns to Use with Other Functions – SUMIFS, AVERAGEIFS, and FILTER

View the Conditional Maximum and Detailed List at the Same Time

After finding the maximum sales amount with MAXIFS in cell H2, use FILTER to return the matching row.

=FILTER(A2:E11,
        (B2:B11="셔츠")*
        (C2:C11="서울")*
        (D2:D11="온라인")*
        (E2:E11=H2))

Conditional Summary Report Template Idea

Create a table that summarizes maximum/minimum sales by product, region, and channel, then use formulas such as the following:

=MAXIFS($E$2:$E$11, $B$2:$B$11, $A2, $C$2:$C$11, $B2, $D$2:$D$11, $C2)

=MINIFS($E$2:$E$11, $B$2:$B$11, $A2, $C$2:$C$11, $B2, $D$2:$D$11, $C2)

Wrap-Up – Extend It to Report Automation

With MAXIFS and MINIFS, you can now easily automate a variety of reports, including sales data analysis, promotion performance analysis, highest/lowest price monitoring, and employee evaluation score analysis.

Related articles:

• Master Conditional Sums with the Excel SUMIFS Function
• Sum Only Filtered Data with the Excel SUBTOTAL Function
• A Practical Guide to Finding Values with Multiple Criteria in Excel VLOOKUP