Two new functions has been released by Microsoft quite recently, namely ‘MINIFS’ and ‘MAXIFS’. These two functions will alter the way in which you find the minimum and the maximum values within a cell range. 

Earlier, it was essential to create an array formula by combining the ‘MIN’ or ‘MAX’ function with the component of ‘IF’ function, if the minimum or maximum value had to be found within a cell range.

For beginners, this implied the use of 2 functions simultaneously and the need to understand the creation of array formula. Both of these functions are not difficult to use, but are however a bit difficult for a new user. 

The result is the same when one selects a value list such as ‘=MAX (1,2,3,4,5)’ or a cell references range which contains the values such as ‘=MAX (A2:A6)’. The same can changed by the addition of ‘IF’ function to enforce on the range which is examined by the ‘MIN’ or ‘MAX’ function , a few criteria  to act as filter.

When you are imposing criteria such as, the minimum value must be greater than two in the A2 to A6 range, the array formula would appear like ‘=MIN(IF(A2:A6 > 2,A2:A6)).

Enter ‘MINIFS’ and ‘MAXIFS’

The ‘MAXIFS’ as well as the ‘MINIFS’ function allows you to execute “filtered” versions of ‘MAX’ and the ‘MIN’ within the function design itself.

Following is the syntax of ‘MINFS’ and MAXIFS’:

‘MINIFS (min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)’

 1:  ‘min_range’ is our first parameter and is the range from where the minimum value is sought.

2:  ‘criteria_range1’ is the cell range which will impose criteria. It can be identical to ‘min_range’ or can be something entirely different. The ‘criteria_range1’ should of the same shape as well as size as the ‘min_range’.

3: The ‘criteria1’ is the criteria that you desire to execute upon the ‘criteria_range’. It can take the form of a number, text or even an expression.

The same parameters are applicable to ‘MAXIFS’.