MINIFS function in Microsoft Excel – syntax and formula examples

This article explores advanced methods for finding the minimum value in Excel based on one or more conditions, comparing the traditional MIN and IF combination with the newer MINIFS function. Let's delve into the efficient ways to locate the smallest number under various circumstances.

We'll cover:

• Finding the minimum with multiple conditions.
• Utilizing MINIFS for efficient minimum value searches with single or multiple criteria.
• Understanding the MINIFS syntax.
• Applying MINIFS for single-criterion minimums.
• Finding minimums based on multiple criteria with MINIFS.
• Identifying the smallest non-zero number using MINIFS.
• Using MINIFS with partial matches.

Multiple Conditions with MIN and IF

Previously, we showed how to find the minimum value based on a single criterion using MIN and IF. However, for more complex searches involving multiple requirements, nesting multiple IF functions or using the multiplication operator (*) within an array formula becomes necessary. For example, to find the lowest quantity of apples sold in a specific region:

{=MIN(IF(A2:A15=F2,IF(C2:C15=F3,D2:D15)))} or {=MIN(IF((A2:A15=F2)*(C2:C15=F3),D2:D15))}

Remember: These are array formulas (Ctrl Shift Enter).

MINIFS: A Simpler Solution

MINIFS, available in Excel 2019 and Office 365, simplifies finding minimum values based on multiple criteria. It combines the functionality of MIN and IF into a single, more intuitive function.

MINIFS Syntax:

=MINIFS(min_range, range1, criteria1, [range2], [criteria2], ...)

• min_range: The range containing the numbers from which to find the minimum.
• range1, criteria1: The range to evaluate against the first criterion and the criterion itself.
• [range2], [criteria2], ...: Optional additional ranges and criteria (up to 126).

MINIFS Examples:

Single Criterion: To find the minimum value where column A equals "D2":

=MINIFS(B2:B15, A2:A15, D2)

Multiple Criteria: To find the minimum value where column A equals "F2" AND column C equals "F3":

=MINIFS(D2:D15, A2:A15, F2, C2:C15, F3)

Important: min_range and all criteria ranges must be the same size.

Minimum Without Zeroes:

To find the smallest non-zero value:

=MINIFS(B2:B15, B2:B15, ">0")

Partial Matches:

Use wildcards (*) for partial matches: To find the minimum value where column A starts with "Apple":

=MINIFS(C2:C15, A2:A15, "Apple*")

MINIFS offers a streamlined approach compared to the MIN and IF combination, especially when dealing with multiple conditions. While the older method remains functional, MINIFS provides a more efficient and readable solution for finding minimum values in Excel. For finding the Nth lowest value with criteria, consider the SMALL IF function.

The above is the detailed content of MINIFS function in Microsoft Excel – syntax and formula examples. For more information, please follow other related articles on the PHP Chinese website!