MINIFS
The MINIFS function returns the smallest numeric value in a range of cells, determined by a set of criteria.
MINIFS(range, criteria-range, criteria, criteria-range…, criteria…)
range: Any value or collection containing any values. Date/time values or duration values can’t be mixed with other value types. If either of these value types is included, all values must be of the same value type. A string value or boolean value can be included in a referenced cell, but can’t be entered directly as an argument to the function.
criteria-range: The cells to be evaluated using criteria.
criteria: The value that is tested against criteria-range to determine the smallest value.
criteria-range…: An optional range of cells to be evaluated using criteria….
criteria…: An optional value that is tested against criteria-range… to determine the smallest value.
Notes
MINIFS returns 0 if none of the criterion are satisfied.
range and criteria-range (or range and criteria-range…) must be the same size, otherwise an error is returned.
Examples |
|---|
Given the following table: |
A | B | |
|---|---|---|
1 | Grade | Weight |
2 | 94 | 1 |
3 | 82 | 2 |
4 | 97 | 3 |
5 | 89 | 2 |
6 | 80 | 3 |
=MINIFS(A2:A6, B2:B6, 2) returns 82, the lowest grade with a weight of 2. =MINIFS(A2:A6, B2:B6, ">1") returns 80, the lowest grade with a weight greater than 1. =MINIFS(A2:A6, B2:B6, "<>3") returns 82, the lowest grade with a weight that is not 3. You can also use the ≠ operator. |
Example using REGEX |
|---|
Given the following table: |
A | B | |
|---|---|---|
1 | 45 | marina@example.com |
2 | 41 | Aaron |
3 | 32 | michael@example.com |
4 | 29 | katrina@example.com |
5 | 12 | Sarah |
=MINIFS(A1:A5, B1:B5, REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"), A1:A5, ">10") returns 29, the minimum of the values in A1:A5 that are larger than 10 and where the corresponding cell in B1:B5 contains an email address. |