Exceljet

Quick, clean, and to the point

Excel MINIFS Function

Excel MINIFS function
Summary 

The Excel MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values. MINIFS can be used with criteria based on dates, numbers, text, and other conditions. MINIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 
Get minimum value with criteria
Return value 
Minimim value
Syntax 
=MINIFS (min_range, range1, criteria1, [range2], [criteria2], ...)
Arguments 
  • min_range - Range of values used to determine minimum.
  • range1 - The first range to evaluate.
  • criteria1 - The criteria to use on range1.
  • range2 - [optional] The second range to evaluate.
  • criteria2 - [optional] The criteria to use on range2.
Usage notes 

The MINIFS function can be used to find a minimum value in data after applying one or more criteria. Conditions are applied using range/criteria pairs. MINIFS can handle up to 126 range/criteria pairs. Each criteria range supplied must be the same size as the min_range.

The MINIFS function can apply criteria to dates, numbers, and text. MINIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

MINIFS will automatically ignore empty cells, even when criteria match. In other words, MINIFS will not treat empty cells that meet criteria as zero. On the other hand, MINIFS will return zero (0) if no cells match criteria.

MINIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and MINIFS requires a cell range for range arguments, you can't use an array.

MINIFS is a new function, available in Excel 365 and Excel 2019. In earlier versions of Excel you can use an array formula based on MIN and IF to find minimum values with criteria.

Example

In the example shown, the formula in H7 is:

=MINIFS(D3:D15,C3:C15,"F")

MINIFS returns the minimum value in D3:D15 where C3:C15 is equal to "F".

Notes

  • MINIFS will return zero (0) if no cells match criteria.
  • Criteria range(s) must be the same size as the min_range.
  • MINIFS will return a #VALUE error if a criteria range is not the same size as the min_range.