# 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 […]

This article describes how to filter records based on the maximum value of a specific item. There are names in […]

This article demonstrates formulas that lets you extract the smallest number larger than a given number. The example above specifies […]

### 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.

**Contact Oscar**

You can contact me through this contact form

For MINIFS why was there no mention what the formula returns when the multiple criteria is not met?