# 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. madhavan says:

Great you saved my time

2. Volker Hetzer says:

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

3. Puja says:

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))))

4. Keith says:

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.

5. Arthur J. Smith says:

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. Arthur J. Smith says:

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.