How to use the MINIFS function
What is the MINIFS function?
The MINIFS function calculates the smallest value based on a given set of criteria.
Related functions to the MINIFS function
| Function | Description |
|---|---|
| MIN(number1,number2,...) | Returns the smallest numeric value |
| MINA(value1,value2,...) | Returns the smallest value including text |
| MINIFS(min_range, criteria_range1, criteria1,...) | Returns min from a range meeting multiple criteria |
| SMALL(array, k) | Returns the k-th smallest value in array |
| AGGREGATE(4,options,array,k) | Returns k-th smallest value ignoring hidden rows/columns |
| DMIN(database, field, criteria) | Returns min value in a database meeting criteria |
Table of Contents
- MINIFS Function Syntax
- MINIFS Function arguments
- MINIFS function example
- MINIFS Function not working
- MINIFS function multiple criteria ex 1
- MINIFS function multiple criteria ex 2
- MINIFS function greater than 0 (zero)
- MINIFS function date
- MINIFS function not blank
- Get the smallest number larger than a given number and a condition
- Get the smallest number larger than a given number and criteria
- Get Excel *.xlsx file
1. MINIFS Function Syntax
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
2. MINIFS Function Arguments
| min_range | Required. A cell reference pointing to the numbers. |
| criteria_range1 | Required. Cells to evaluate based on the criteria. |
| criteria1 | Required. Criteria in the form of a number, expression, or text. |
| [criteria_range2] | Optional. Up to 126 additional arguments. |
| [criteria2] | Optional. Up to 126 additional arguments. |
3. MINIFS Function Example
The formula in cell E3 checks if values in column B are equal to "A". If TRUE corresponding values in column C are evaluated and the minimum value is then returned to cell E3.
Formula in cell E3:
The cells in cell range B3:B10 that match condition "A" are B4, B7, B9, and B10. The corresponding values on the same row in cell range C3:C10 are 0, -3, -5, and 5. The smallest value is -5 which is the value returned in cell E3.
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
min_range - C3:C10
criteria_range1 - B3:B10
criteria1 - "A"
4. MINIFS function not working
The MINIFS function returns #VALUE error if the cell range size of the min_range, criteria_range and criteria is not the same. The image above shows a formula with the first argument being C3:C9 and the second argument is B3:B10. They clearly have different sizes, C3:C9 has seven rows and B3:B10 has eight rows. They must match in size.
5. MINIFS function multiple criteria ex 1
This example demonstrates how to use multiple criteria in the MINIFS function. Cell range B3:B10 and C3:C10 contain text values, cell range D3:D10 contains numbers. The criteria are specified in cells F3 and G3.
Formula in cell F7:
The formula in cell F7 returns -7, here is how:
The first condition is specified in cell F3 and it matches cells B5, B8, and B10. The second condition is specified in cell G3 and it matches cells C5, C6, C8, C9, and C10.
Both conditions on the same row must match to be included in the calculation. Rows 5, 8, and 10 have both conditions matching and the corresponding numbers in cell range D3:D10 are -7, 3, and 5.
The smallest number of -7, 3. and 5 is -7.
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
min_range - D3:D10
criteria_range1 - B3:B10
criteria1 - F3
criteria_range2 - C3:C10
criteria2 - G3
6. MINIFS function multiple criteria ex 2
This example demonstrates how to use criteria applied to one column, in this case, OR logic meaning one of the criteria values must match. The criteria are specified in cells E3 and E4.
Formula in cell E7:
Criteria "A" and "C" match cells B4, B5, B7, B9, and B10, the corresponding values on the same rows in C3:C10 are 0, -7, -3, -5, and 5. The smallest value is -7 which is the value returned in cell E7.
6.1 Explaining formula
Step 1 - Populate arguments
min_range - C3:C10
criteria_range1 - B3:B10
criteria1 - E3:E4
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
becomes
MINIFS(C3:C10,B3:B10,E3:E4)
Step 2 - Evaluate MINIFS function
MINIFS(C3:C10,B3:B10,E3:E4)
becomes
MINIFS({5;0;-7;-1;-3;3;-5;5},{"B";"A";"C";"B";"A";"B";"A";"A"},{"A";"C"})
and returns the following array: {-5;-7}
-5 is the calculated value for condition "A" and -7 is the calculated value for the second condition "C"
Step 3 - Smallest number in array
The MIN function returns the smallest number in a cell range.
Function syntax: MIN(number1, [number2], ...)
MIN(MINIFS(C3:C10,B3:B10,E3:E4))
becomes
MIN({-5;-7})
and returns -7
7. MINIFS function greater than 0 (zero)
This example shows how to apply three different conditions, one of them being numbers above 0 (zero):
- Condition in cell F3, "Blue" : B3:B10
- Condition in cell G3, "Small" : C3:C10
- Above 0 (zero) : D3:D10
Formula in cell E7:
Cells B5, B8, and B10 match the first condition specified in cell F3, cells B5, B6, B8:B10 match cell G3. Cell values above zero are D3, D8, and D10.
Rows matching all criteria are 8 and 10. These cells contain 3 and 5, the smallest number is 3 which is displayed in cell F7.
8. MINIFS function smallest number based on date condition
This example demonstrates how to find the smallest number if the corresponding date is earlier or "smaller" than a given date specified in cell E3.
Dates in Excel are stored as sequential serial numbers with January 1, 1900 as serial number 1. The whole number represents the date, while the decimal portion represents the time.
For example, the date January 1, 2022 is stored as 44,562 since it is 44,562 days after January 1, 1900. This makes it easy to perform date calculations.
Formula in cell E7:
Cells containing a date earlier than the date given in cell E3 are B6, B9, and B10. The corresponding values in C3:C10 are-1, -5. and 5. The smallest number is -5.
9. MINIFS function smallest number if corresponding cell on the same row is not blank
This example demonstrates how to ignore numbers based on blank values in cell range C3;C10 and a condition specified in cell F3 applied to cell range B3:B10.
Formula in cell F7:
Cells in cell B3:B10 equal to "Blue" specified in cell F3 are B5, B7, B8, and B10. Cells not blank in C3:C10 are B3:B4, B6:B7, and C9:C10.
Corresponding numbers in cell range D3:D10 are -3 and 5. The smallest number is -3.
10. Get the smallest number larger than a given number and a condition
This example demonstrates how to get the smallest number larger than a given number and also based on a condition. Cell G5 contains the lower number limit, meaning the numbers in C5:C10 must be larger to be a match. Cell G4 contains a condition, this condition must be found in B5:B10 to be a match.
Both the lower number limit and the condition must be valid for the number on the same row to be included in the MINIFS calculation.
Formula in F9:
The MINIFS function calculates the smallest value based on a given set of criteria. This example has a lower number limit of 3 and the condition is "B".
Cells B6, B10 matches the condition "B", cells C8, C9, and C10 contains numbers larger than "3". Row 10 is the only row that matches both the condition and the number. The value in row 10 is 3.06 which matches the number in cell F9.
The following formula returns the value from "Col C" on the same row based on the conditions specified above. This value is 6.36 which also matches the output in cell G9.
Formula in cell G9:
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
min_range - Required. A reference to the numbers.
criteria_range1 - A reference to cells to evaluate based on the criteria.
criteria1 - Criteria in the form of a number, expression, or text.
11. Get the smallest number larger than a given number and criteria
This example demonstrates how to filter numbers based on a date range, in other words, a start and end date defines the range. The start date is specified in cell G4, and the end date in G5. It also uses a lower number limit specified in cell G6. All conditions must match, in other words, AND logic.
Formula in cell F10:
The date range is between January 1st and July 1st, the number limit is 3. The rows that match are only row 9, the value is 3.04 which is the same as in cell F10.
The formula in cell G10 returns 4.2 because it is smaller than the number in D9 and matches all conditions.
2.1 Explaining formula in cell F10
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Step 1 - First condition
The first criteria_range1 is C5:C10, the first criteria1 is ">"&G6.
The ampersand character concatenates the larger than character and the condition specified in cell G6.
C5:C10,">"&G6
The first criteria pair checks if the values in C5:C10 are larger than the value in cell G6.
The image above highlights which cells contain a number larger than the condition.
Step 2 - Second condition
The second criteria_range2 is B5:B10, the second criteria2 is ">="&G4.
The ampersand character concatenates the larger than character, the equal sign, and the condition specified in cell G4.
B5:B10,">="&G4
The second criteria pair checks if the dates in B5:B10 are later or equal to the date in cell G4.
The image above shows that all dates are equal or later than the start range date specified in cell G4.
Step 3 - Third condition
The third criteria_range2 is again B5:B10, the third criteria2 is "<="&G5.
The ampersand character concatenates the less than character, the equal sign, and the condition specified in cell G4.
B5:B10,"<="&G5
The second criteria pair checks if the dates in B5:B10 are later or equal to the date in cell G4.
The image above highlights dates equal to or earlier than the start range date specified in cell G4.
Step 4 - Evaluate MINIFS function
The MINIFS function calculates the smallest value based on a given set of criteria.
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
MINIFS(C5:C10,C5:C10,">"&G6,B5:B10,">="&G4,B5:B10,"<="&G5)
returns 3.04 in cell F10. When all conditions are met value 3.04 is returned.
Formula in cell G10
MINIFS(D5:D10,D5:D10,">"&G6,B5:B10,">="&G4,B5:B10,"<="&G5)
returns 4.2. Values 4.2 and 5.33 meet all conditions, however, 4.2 is the smallest.
Get Excel *.xlsx file
'MINIFS' function examples
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Table of Contents Find and return the highest number and corresponding date based on a condition Lookup value based on […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
Functions in 'Statistical' category
The MINIFS function function is one of 73 functions in the 'Statistical' category.
Excel function categories
Excel categories
One Response to “How to use the MINIFS function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
For MINIFS why was there no mention what the formula returns when the multiple criteria is not met?