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.

Great you saved my time

Can you make it work with something other than =?

In particular, =MIN((A2:A30K5)*(F2:F30)) doesnt work.

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

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.

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

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.