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
In the example shown, the formula in H7 is:
=MINIFS(D3:D15,C3:C15,"F")
- Criteria range(s) must be the same size and shape as the min_range.
- The MINIFS function was introduced in Excel 2016.
- In earlier versions of Excel you can use an array formula based on MIN and IF to find minimum values with criteria.
MINIFS formula examples
To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH to display the event on that date. In the...
To get a minimum value based on criteria, you can use the MIN function together with the IF function. In the example shown, the formula in G6 is:
{=MIN(IF(names=F6,times))}
Where "names" is the named range B6:B17,...
To calculate the minimum difference between two sets of values, ignoring cases where either value is blank, you can use an array formula based on the MIN and IF functions. In the example shown, the formula in F4 is...
To get the minimum value in a data set using multiple criteria (i.e. to get MIN IF), you can use and array formula based on the MIN and IF functions. In the example shown the formula in I6 is:
{=MIN(IF(color=G6,IF(...
To get "first in, last out" times from timesheet data, you can use the MAXIFS and MINIFS functions, or an array formula as described below. In the example shown, the formula in H6 is:
=MINIFS(times,names,H5,actions,...
Related functions
Good links
