MINIFS function

MINIFS(targetArray, conditionArray, condition, otherConditionArray..., otherCondition...) MINIFS(targetArray; conditionArray; condition; otherConditionArray...; otherCondition...)

targetArray — { Number }

The array containing the numbers to evaluate. Only numbers corresponding to elements of the condition arrays which satisfy all the given conditions are considered. Two elements are said to correspond to each other if they are in the same position in their respective arrays.

conditionArray — { Number }, { Logical }, { Text } or { Color }

The array containing the values to test. Only the numbers of the first array, targetArray, which correspond to the elements of this array which satisfy the corresponding condition and also satisfy the conditions of all the other condition arrays, are used in the final calculation. Two elements are said to correspond to each other if they are in the same position in their respective arrays.

condition

A text string specifying the condition, like ">1"">1", a number which array elements must be equal to or a formula fragment specifying the condition.

If a text string is given, the following operators are supported: < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), = (equality, case-insensitive for text), == (equality, case-sensitive for text), <> (inequality, case-insensitive for text) and != (inequality, case-sensitive for text). "" means "only blank values," "<>" and "!=" mean "only non-blank values."

If a formula fragment is given, it must return a logical value (TRUE or FALSE) specifying whether the element under consideration should be included. To do its work, it has access to the values Element (the array element currently being tested), Index (the numeric position of the array element currently being tested, starting at 1) and Source, the corresponding condition array.

otherConditionArray — { Number }, { Logical }, { Text } or { Color } (accepts many)

An additional array containing values to test. The condition is specified as the next parameter.

otherCondition — Text, Number, Logical, Color, { Text }, { Number }, { Logical } or { Color } (accepts many)

A condition for testing elements of the preceding array. Refer to the documentation for the condition parameter for more information.

Returns — Number or { Number }

The smallest number of all array elements which satisfy one or several conditions.

Returns the smallest number of all array elements which satisfy one or several conditions. MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, ">1", { "Eve", "Eve", "Bill" }, "Eve")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; ">1"; { "Eve"; "Eve"; "Bill" }; "Eve") returns 20, because that is the only element of the first array where both corresponding elements in the other arrays satisfy their conditions.

In the second array { 1, 2, 3 }{ 1; 2; 3 }, 2 and 3 satisfy the ">1"">1" condition, as they are both greater than 1. In the third array, { "Eve", "Eve", "Bill" }{ "Eve"; "Eve"; "Bill" }, the two first elements satisfy the "Eve""Eve" condition, requiring matching elements to be equal to "Eve". In other words, the second and third elements of the second array match their corresponding condition, while the first and second elements of the third array match their corresponding condition. As such, only the second element of the first array is considered, resulting in a return value of 20.

Use & to reference values that reside elsewhere. This formula only considers those elements of the first array which are greater than the value of Field1:

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, ">" & Field1)MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; ">" & Field1)

MINIFS works with multiple conditions, but it only requires a single condition. There is no function named MINIF.

Writing conditions

MINIFS supports the following operators: < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), = (equality, case-insensitive for text), == (equality, case-sensitive for text), <> (inequality, case-insensitive for text) and != (inequality, case-sensitive for text).

If a condition parameter is set to "", only the blank values of the corresponding array are considered. If it is set to "<>" or "!=", all non-blank values are considered.

If the operator is left out, "=" is assumed. As a result, these formulas are equivalent:

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, "=20")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; "=20")
MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, "20")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; "20")
MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, 20)MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; 20)

The language settings of the app are taken into account when parsing numbers that are part of a condition text string, meaning that an app configured to use German should use a decimal comma (",") as a decimal separator and an app configured to use Australian English should use a decimal point ("."). Thousands separators should not be used and negative numbers should use a leading minus sign ("-").

Specifying the condition using a formula fragment

The condition parameters can also be formula fragments. These formulas are equivalent:

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, ">1")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; ">1")
MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, Element > 1)MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; Element > 1)

Using a formula fragment gives you access to the full power of the formula language. Consider this formula:

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, (Element > 1) && ISODD(Element))MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; (Element > 1) && ISODD(Element))

The formula above returns 30, because only array elements are considered which are greater than 1 and are odd numbers, which disqualifies 1 and 2, leaving only 3 (corresponding to 30 in the first array).

&& means logical "and," || means logical "or" and ! means logical negation.

This formula returns 10, as 1, 2 and 3 are all considered:

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, (Element > 1) || ISODD(Element))MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; (Element > 1) || ISODD(Element))

1, 2 and 3 are all considered due to the || operator, which causes all array elements to be considered which are either greater than 1 or are odd (1, 2 and 3 correspond to 10, 20 and 30 in the first array).

This formula returns 20, as only 2 is both greater than 1 and is "not odd" (even):

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, (Element > 1) && !ISODD(Element))MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; (Element > 1) && !ISODD(Element))

! in !ISODD(Element)!ISODD(Element) negates the value returned from ISODD, causing TRUE to become FALSE and vice versa.

The formula fragment is run once for every array element to be tested, and is expected to return TRUE if the element should be considered, and FALSE otherwise. To do its work, it has access to the values Element (the element of the corresponding array currently being tested), Index (the numeric position of the array element currently being tested, starting at 1) and Source, the corresponding array.

If MINIFS is used with multiple conditions, either all or none may use formula fragments.

Using wildcards in the condition to match text flexibly

When a condition array contains text strings, the condition can use special characters to match multiple text strings. This formula returns 10, because both "car" and "cat" match the wildcard "ca?":

MINIFS({ 10, 100 }, { "car", "cat" }, "ca?")MINIFS({ 10; 100 }; { "car"; "cat" }; "ca?")

? matches any character. "ca?" matches not only "car" and "cat", but also "cab", "can" and "cap". However, it does not match "card", because ? matches exactly one character, nor does it match "ca".

* matches no character, a single character, or multiple characters. As such, "ca*" matches everything that "ca?" matches, but also "card", "ca" and "Canada" (matching is case-insensitive).

? and * can be combined in the same pattern. Colo*r ad*s are great? matches both "Color ads are great!" and "Colour adverts are great?".

Sometimes, the special characters ? and * need to be included as-is, and not have special meaning. To achieve that, precede each special character with a tilde ("~"). This formula returns 100, because only "ca*" in the second array matches "ca~*":

MINIFS({ 10, 100 }, { "cat", "ca*" }, "ca~*")MINIFS({ 10; 100 }; { "cat"; "ca*" }; "ca~*")

In other words, to search for a verbatim "*" character, write "~*". To search for a verbatim "?" character, write "~?". Finally, to search for a verbatim "~" character, write "~~".

Regular expressions are much more powerful than wildcards, but are also harder to learn. Use REGEXMATCH in a formula fragment to use regular expressions with MINIFS.

This formula returns 100, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three numbers):

MINIFS({ 100, 10 }, { "123", "ab3" }, REGEXMATCH(Element, "\d\d\d"))MINIFS({ 100; 10 }; { "123"; "ab3" }; REGEXMATCH(Element; "\d\d\d"))

Refer to the documentation of REGEXMATCH for more information.

Using FILTER, MIN and REDUCE instead of MINIFS

The work MINIFS performs can be divided into two parts: filtering out the unwanted elements from the condition arrays and then returning the smallest of the elements of the first array which correspond to the ones in the condition arrays which made the cut. As such, these formulas are equivalent:

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, ">1")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; ">1")
MIN(FILTER({ 10, 20, 30 }, { 1, 2, 3 } > 1))MIN(FILTER({ 10; 20; 30 }; { 1; 2; 3 } > 1))

The version using FILTER provides more flexibility, as the final step (determining the smallest number through MIN) can be replaced by another function (like PRODUCT, which multiplies the remaining numbers together), independent of the filtering logic.

Of course, MINIFS is normally used with multiple conditions. FILTER also supports multiple conditions using the && or * operator. These formulas are equivalent:

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, ">1", { "Eve", "Eve", "Bill" }, "Eve")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; ">1"; { "Eve"; "Eve"; "Bill" }; "Eve")
MIN(FILTER({ 10, 20, 30 }, { 1, 2, 3 } > 1 && { "Eve", "Eve", "Bill" } = "Eve"))MIN(FILTER({ 10; 20; 30 }; { 1; 2; 3 } > 1 && { "Eve"; "Eve"; "Bill" } = "Eve"))

For even more flexibility, consider replacing MIN with REDUCE. Refer to the example below for more information.

Examples

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, ">1", { "Eve", "Eve", "Bill" }, "Eve")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; ">1"; { "Eve"; "Eve"; "Bill" }; "Eve")

Returns 20, because that is the only element of the first array where both corresponding elements in the other arrays satisfy their conditions.

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, ">1")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; ">1")

Returns 20. It does not return 10, as only 2 and 3 of the second array are greater than 1, as specified by the ">1"">1" parameter.

MINIFS({ 10, 20, 30 }, { "Bill", "Eve", "Eve" }, "Eve")MINIFS({ 10; 20; 30 }; { "Bill"; "Eve"; "Eve" }; "Eve")

Returns 20, as only 20 and 30 correspond to "Eve" in the second array.

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, ">" & Field1)MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; ">" & Field1)

Returns the smallest number in the { 10, 20, 30 }{ 10; 20; 30 } array corresponding to a value of the second array which is greater than the value of Field1. If Field1.ValueField1,Value is 1, 20 is returned.

MINIFS({ 11, 22, 33 }, Field1:Field3, ">1")MINIFS({ 11; 22; 33 }; Field1:Field3; ">1")

Returns the smallest of the elements of the { 11, 22, 33 }{ 11; 22; 33 } array, where only elements corresponding to elements in the Field1:Field3Field1:Field3 range greater than 1 are considered.

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, "=2")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; "=2")

Returns 20, which corresponds to 2 in the array { 1, 2, 3 }{ 1; 2; 3 }, which is the only element of that array which satisfies the "=2""=2" condition.

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, "2")MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; "2")

Returns 20, which corresponds to 2 in the array { 1, 2, 3 }{ 1; 2; 3 }, which is the only element of that array which satisfies the "2""2" condition. Equality is implied when no operator is specified.

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, 2)MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; 2)

Returns 20, which corresponds to 2 in the array { 1, 2, 3 }{ 1; 2; 3 }, which is the only element of that array which satisfies the 22 condition. When a plain value is given as a condition, equality is implied. If only values equal to another value should be considered, using a plain value instead of a value enclosed in a text string is preferable, as errors can be detected at an earlier stage.

MINIFS({ 11, 12, 13 }, { 1.1, 1.2, 1.3 }, ">1.1")MINIFS({ 11; 12; 13 }; { 1,1; 1,2; 1,3 }; ">1.1")

Returns 12. It does not return 11, as only 1.2 and 1.3 are greater than 1.1, as specified by the ">1.1"">1.1" parameter. A decimal separator appropriate for the language of the app should be used. If that language is French, for instance, a decimal comma should be used ("1,1" instead of "1.1").

MINIFS({ 10, 100 }, { "car", "cat" }, "ca?")MINIFS({ 10; 100 }; { "car"; "cat" }; "ca?")

Returns 10, because both "car" and "cat" match the wildcard "ca?". ? matches exactly one (arbitrary) character.

MINIFS({ 10, 100 }, { "car", "card" }, "ca*")MINIFS({ 10; 100 }; { "car"; "card" }; "ca*")

Returns 10, because both "car" and "card" match the wildcard "ca*". * matches zero, one or several (arbitrary) characters.

MINIFS({ 10, 100 }, { "car", "ca*" }, "ca~*")MINIFS({ 10; 100 }; { "car"; "ca*" }; "ca~*")

Returns 100, because only "ca*" matches the wildcard "ca~*". ~ written before any other character matches it verbatim, meaning that * does not have special meaning here.

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, Element > 1)MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; Element > 1)

Returns 20. It does not return 10, as only 2 and 3 of the second array are greater than 1, as specified by the Element > 1Element > 1 parameter. This example uses a formula fragment instead of a text string. It is invoked once for every tested array element, and is expected to return TRUE if the value should be considered and FALSE otherwise. To do its work, it has access to the Element value, which is the value under consideration.

MINIFS({ 10, 20, 30 }, { 1, 2, 3 }, (Element > 1) && ISODD(Element))MINIFS({ 10; 20; 30 }; { 1; 2; 3 }; (Element > 1) && ISODD(Element))

Returns 30, as only 3 in the second array is both greater than 1 and is an odd number and it corresponds to 30 in the first array. Formula fragments have access to the full power of the formula language, enabling complex conditions. && means logical "and," || means logical "or" and ! means logical negation.

MIN(FILTER({ 10, 20, 30 }, { 1, 2, 3 } > 1))MIN(FILTER({ 10; 20; 30 }; { 1; 2; 3 } > 1))

Returns 20. It does not return 10, as only 2 and 3 of the second array are greater than 1. FILTER returns a version of the first array, where elements corresponding to elements of the second array which failed the > 1 test are not present. Finally, MIN returns the smallest number.

MIN(FILTER({ 10, 20, 30 }, { 1, 2, 3 } > 1 && { "Eve", "Eve", "Bill" } = "Eve"))MIN(FILTER({ 10; 20; 30 }; { 1; 2; 3 } > 1 && { "Eve"; "Eve"; "Bill" } = "Eve"))

Returns 20, because that is the only element of the first array where both corresponding elements in the other arrays satisfy their conditions. The && operator enables FILTER to use multiple conditions.

MINIFS({ 10, 100 }, { "ab3", "123" }, REGEXMATCH(Element, "\d\d\d"))MINIFS({ 10; 100 }; { "ab3"; "123" }; REGEXMATCH(Element; "\d\d\d"))

Returns 100, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three numbers). Refer to the documentation of REGEXMATCH for more information.