MINIF and MAXIF in Excel 2003 or 2007

Like COUNTIF and SUMIF, it is helpful to have a conditional MAX or MIN. Excel doesn’t have it for now (yeah, “for now”, ‘coz MS has steadily been adding such useful functions with every new release).

Well, the good news is that there is a work-around.

MAXIF
The approach is to have an array formula on the IF column returning a bool, and multiplying it with the column from which MAX is to be found.

Say, we need to find the MAX in column F based on a condition on Column A and K5 having the compare value. We can do this as below.

=MAX((A2:A30=K5)*(F2:F30))
Since it is an array formula, do a Ctrl+Shift+Enter and it’ll become {=MAX((A2:A30=K5)*(F2:F30))}. Do NOT manually add the braces.

MINIF
This is very similar to what we did in the above activity. Just replace MAX with MIN.

=MIN((A2:A30=K5)*(F2:F30))

Again, this is an array formula and need to do Ctrl+Shift+Enter.

Note that empty cells in column F might break this formula.

7 thoughts on “MINIF and MAXIF in Excel 2003 or 2007

  1. Can you make it work with something other than =?
    In particular, =MIN((A2:A30K5)*(F2:F30)) doesnt work.

  2. For more than one criteria, you can try following

    =MAX(IF(‘Production Status’!F:F=”Category 1″,(IF(‘Production Status’!K:K=$A$2,’Production Status’!J:J))))

  3. this only works if your data spans positive and negative values.

    If all data are greater than zero, the MIN function returns zero (FALSE * {any value} = 0)
    If all data are less than zero, the MAX fuction returns zero.

    i thought they had added the MaxIF and MinIF functions to XL2007? an add-in perhaps? yet i can’t find it either.

  4. I have successfully used the following VBA function in the Personal.xls file:
    Public Function MaxIf(Range As Excel.Range, Criteria As Excel.Range, Max_Range As Excel.Range, Optional Default As Double = 0, Optional Add_Range As Excel.Range) As Double
    ‘this scans the range for match with criteria and returns maximum of sum of Max_Range and Add_Range values
    ‘error checking
    Dim nRow As Long, nCol As Long
    nRow = Range.Rows.Count
    nCol = Range.Columns.Count
    If nRow Max_Range.Rows.Count Or nCol Max_Range.Columns.Count Then
    Exit Function
    End If
    If Not Add_Range Is Nothing Then
    If nRow Add_Range.Rows.Count Or nCol Add_Range.Columns.Count Then
    Exit Function
    End If
    End If
    ‘scan the array
    Dim First As Boolean
    First = True
    Dim iRow As Long, icol As Long
    Dim crit As Range, val As Double
    For Each crit In Criteria ‘allows multiple criteria to be used
    If crit.FormulaR1C1 “” Then
    For iRow = 1 To nRow
    For icol = 1 To nCol
    If Range.Cells(iRow, icol).Value = crit.Value Then
    val = Max_Range.Cells(iRow, icol).Value
    If Not Add_Range Is Nothing Then
    val = val + Add_Range.Cells(iRow, icol).Value
    End If
    If First Or MaxIf < val Then
    MaxIf = val
    First = False
    End If
    End If
    Next
    Next
    End If
    Next
    If First Then MaxIf = Default 'none found
    End Function

    1. All of the “not equal” symbols were interpreted as HTML tags and removed from the above code as in the following:
      If nRow “not equal” Max_Range.Rows.Count Or nCol “not equal” Max_Range.Columns.Count Then
      If nRow “not equal” Add_Range.Rows.Count Or nCol “not equal” Add_Range.Columns.Count Then
      If crit.FormulaR1C1 “not equal” “” Then

      Also clarifying indentation has been removed but you should still be able to get the VBA code to work in Excel 2003 and 2007.

Leave a Reply

Your email address will not be published. Required fields are marked *