How do I find the minimum value in excel given multiple criteria?

Understanding Conditional Aggregation in Excel

When working with large datasets, simply finding the lowest numerical entry rarely provides actionable insight. Instead, analysts frequently need to filter data based on specific conditions before identifying the minimum result. For instance, you might want to find the lowest sales figure, but only for products categorized as “Premium” and sold in the “North” region. Before the introduction of dedicated conditional functions like MINIFS, users relied heavily on complex array formula constructions involving a combination of the MIN and IF functions. While effective, these older formulas were often cumbersome, difficult to audit, and required special entry (Ctrl+Shift+Enter), which could confuse novice users and complicate spreadsheet maintenance.

The introduction of the MINIFS function, starting with Excel 2019 and available in Microsoft 365, dramatically simplified this process. This specialized function allows the user to specify a range to calculate the minimum from, followed by pairs of criteria ranges and the corresponding criteria that must be satisfied. This structure makes the formula far more intuitive, readable, and less error-prone than legacy array formula methods, significantly enhancing productivity for data professionals navigating complex conditional requirements.

The MINIFS Function Syntax Explained

To effectively utilize the MINIFS function, it is essential to understand its precise syntax and the role of each argument. Unlike the older array formulas, the structure of MINIFS is highly logical and scalable, designed to handle multiple conditions seamlessly and efficiently. The basic structure, which dictates the order of arguments, is as follows:

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

The first argument, min_range, is mandatory and specifies the actual range of cells from which the minimum value will be returned. It is crucial that this range contains numerical data, as any non-numeric values are ignored during the calculation. The subsequent arguments are always supplied in pairs. criteria_range1 is the first range that will be evaluated against the condition, and criteria1 is the specific condition that must be met within criteria_range1. These criteria can be numerical values, text strings enclosed in quotation marks, cell references, or logical expressions (like “<5” or “>100”).

You can extend the formula by adding additional pairs—criteria_range2 and criteria2, criteria_range3 and criteria3, and so on—up to 127 pairs in modern versions of Excel. It is imperative that all criterion ranges used in the formula have the exact same number of rows and columns as the primary min_range, although they do not need to be contiguous. If a cell in the min_range meets *all* specified criteria, its corresponding value is included in the set considered for the minimum calculation.

Basic Application of MINIFS with Multiple Criteria

We begin with the fundamental structure required in Excel to pinpoint the minimum value within a range, conditional upon the satisfaction of multiple defined criteria. This core functionality is central to efficient data filtering and retrieval, allowing for complex queries to be resolved instantly.

=MINIFS(C2:C11, A2:A11, "Mavs", B2:B11, "<5")

This specific implementation of the MINIFS function performs two simultaneous checks before determining the minimum. Firstly, it focuses on the numerical values located within the cell range C2:C11, which is designated as the min_range. Secondly, it imposes two necessary conditions: the value in the corresponding row of range A2:A11 (the first criteria range) must strictly equal the text string “Mavs”, AND the value in the corresponding row of range B2:B11 (the second criteria range) must be less than 5. Only the values in C2:C11 that pass both tests simultaneously are considered for the final minimum calculation; all other values are effectively ignored.

Practical Example: Analyzing Player Statistics

To demonstrate the practical utility of the MINIFS function, consider a typical sports dataset containing information about various basketball players. This example dataset tracks player performance metrics such as points scored, assists recorded, and the team they belong to, residing in columns C, B, and A, respectively.

Our objective in this scenario is specific: we aim to identify the minimum value recorded in the “Points” column (C2:C11), but only under two compounding conditions. We require that the corresponding player’s team, located in the “Team” column (A2:A11), must be “Mavs,” and concurrently, the corresponding value in the “Assists” column (B2:B11) must be strictly less than 5. This complex filtering task is efficiently handled by the powerful MINIFS function, bypassing the need for manual data filtering or complicated nested IF statements, thus dramatically increasing calculation speed and reliability compared to older techniques.

Implementing the MINIFS Formula Step-by-Step

Applying the derived syntax to our basketball dataset requires careful mapping of the ranges to the criteria. The “Points” column (C2:C11) serves as our minimum range, the “Team” column (A2:A11) serves as our first criteria range (checked against “Mavs”), and the “Assists” column (B2:B11) acts as our second criteria range (checked against “<5”).

The formula required to achieve our goal, precisely constructed according to the required argument order, is:

=MINIFS(C2:C11, A2:A11, "Mavs", B2:B11, "<5")

We will input this formula into a designated output cell, such as cell E2, to execute the calculation. Once the formula is typed completely, pressing the Enter key (or Return key) will instruct Excel to evaluate the ranges based on the supplied criteria. It is crucial to note the mandatory use of quotation marks around both text criteria (“Mavs”) and comparison operators (“<5”); this convention is essential for Excel to interpret them correctly as conditions rather than direct cell references or numerical constants.

The result of executing this formula in cell E2 is immediate and accurate:

The formula successfully returns the value of 22. This single numerical result represents the minimum points value found among all rows in the dataset that simultaneously satisfy both the team requirement (Team equals “Mavs”) and the assists requirement (Assists is less than 5).

Analyzing and Verifying the Conditional Minimum

Verification is a critical step in any data analysis process to ensure that the conditional logic has been correctly applied. The resulting value of 22 is the minimum points value based on the intersection of the two criteria. It is important to remember that the MINIFS function operates under an inherent AND logic, meaning a row must satisfy *every* condition specified to be included in the minimum calculation. If any single criterion is not met, the corresponding data point is excluded entirely.

We can confirm this outcome by manually reviewing which rows meet both prerequisites within the original dataset structure, as illustrated below:

As highlighted in the visual confirmation, only two rows satisfy the dual conditions specified by the formula:

• The row corresponding to the player with 28 points (Row 8), who is on the “Mavs” team and has 3 assists (3 < 5).
• The row corresponding to the player with 22 points (Row 10), who is also on the “Mavs” team and has 4 assists (4 < 5).

Comparing the points values (28 and 22) derived exclusively from the qualifying rows, 22 is definitively the lowest value. This confirms the accuracy and reliability of the MINIFS function in handling multiple, complex filtering requirements simultaneously, providing significant efficiency improvements over manual checks or legacy array formula methods.

Advanced Criteria and Flexibility

While our core example utilized two standard criteria—an exact text match and a less-than comparison—the MINIFS function is exceptionally flexible and capable of accommodating various other comparison operators and criteria types. You are not limited to just two criteria; you can integrate up to 127 pairs, allowing for extremely detailed and narrow data filtering across numerous dimensions within a large dataset. Common comparison operators that can be employed include:

• Less than or Equal To: "<=50" (Includes values up to 50)
• Greater than or Equal To: ">=10" (Includes values 10 and above)
• Not Equal To: "<>Rockets" (Excludes rows where the Team is exactly “Rockets”)
• Wildcards: "S*" (Matches any text string starting with the letter S)

Furthermore, the criteria itself can be derived dynamically from a cell reference, enhancing the interactivity of your spreadsheet. Instead of hardcoding “Mavs” into the formula, you could reference a cell (e.g., F1) that contains the desired team name. If cell F1 contained “Mavs,” the MINIFS function would be written as: =MINIFS(C2:C11, A2:A11, F1, B2:B11, "<5"). Note that when referencing a cell containing text or a number as the sole criterion, quotation marks around the cell reference itself are usually omitted. However, if the cell reference is concatenated with an operator, such as checking if a value is less than the number in F2, you must wrap the operator in quotes and use the ampersand ("<"&F2). Understanding this adaptability ensures that your Excel models remain dynamic and easy to update, significantly improving the maintainability of complex spreadsheets.

Conclusion

The ability to conditionally calculate minimum values based on multiple criteria is a cornerstone of advanced data manipulation in Excel. The MINIFS function provides a powerful, clear, and efficient solution to this common analytical challenge that surpasses the complexity of older array-based methods. By defining the numerical target range and then precisely pairing criteria ranges with their corresponding conditions, you gain precise control over which data points are included in the aggregation. Whether dealing with financial records, inventory data, or performance statistics, mastering this function significantly enhances your data analysis capabilities. We strongly encourage users to consult the complete official documentation for the MINIFS function to explore its full range of capabilities and compatibility notes, ensuring optimal use within their professional environment.