Excel MAXIF function or emulation?

Question

I have a moderately sized dataset in excel from which I wish to extract the maximum value of the values in Column B, but those that correspond only to cells in Column A that satisfy certain criteria.

The desired functionality is similar to that of SUMIF or COUNTIF, but neither of those return data that is necessary. There isn't a MAXIF function; how do I emulate one?

Answer 1

You can use an array formula.In the cell in which you want the max calculated enter: =Max(If([test],[if true],[if false]) where you replace the values in square brackets with the test, what to return if true and what to return if false. For example:

=MAX(IF(MOD(A2:A25,2)=0,A2:A25,0)

In this formula I return the value in column A if the value divided by 2 has no remainder. Notice that I use a range of cells in my comparison and in the value if false rather than a single cell.

Now, while still editing the cell, hit Ctrl+Shift+Enter (hold down the Ctrl key and the Shift together and then hit enter).

This creates an array formula that acts on each value in the range.

EDIT BTW, did you want to do this programmatically or manually? If programmatically, then what environment are you using? VBA? C#?

EDIT If via VBA, you need to use the FormulaArray property and R1C1 references like so:

Range("A1").Select
Selection.FormulaArray = "=MAX(IF(MOD(R[1]C:R[24]C,2)=0,R[1]C:R[24]C,0))"

Answer 2

Array formulas don't work very well when you want to use dynamic or named ranges (e.g., "the maximum amount due for rows above the current row that have the same counterparty as the current row). If you don't want to use an array formula, you can always resort to VBA to do something like this:

Function maxIfs(maxRange As Range, criteriaRange As Range, criterion As Variant) As Variant

  maxIfs = Empty
  For i = 1 To maxRange.Cells.Count
    If criteriaRange.Cells(i).Value = criterion Then
        If maxIfs = Empty Then
            maxIfs = maxRange.Cells(i).Value
        Else
            maxIfs = Application.WorksheetFunction.Max(maxIfs, maxRange.Cells(i).Value)
        End If
    End If
  Next
End Function

Answer 3

A limitation with the code provided thus far is that you are restricted to 2 conditions. I decided to take this code further to not restrict the number of conditions for the MaxIfs function. Please see the code here:

        Function MaxIfs(MaxRange As Range, ParamArray Criteria() As Variant) As Variant
        Dim n As Long
        Dim i As Long
        Dim c As Long
        Dim f As Boolean
        Dim w() As Long
        Dim k As Long
        Dim z As Variant

        'Error if less than 1 criteria
        On Error GoTo ErrHandler
        n = UBound(Criteria)
        If n < 1 Then
            'too few criteria
            GoTo ErrHandler
        End If
            'Define k
            k = 0            

        'Loop through cells of max range
        For i = 1 To MaxRange.Count

        'Start by assuming there is a match
        f = True

            'Loop through conditions
            For c = 0 To n - 1 Step 2

                'Does cell in criteria range match condition?
                If Criteria(c).Cells(i).Value <> Criteria(c + 1) Then
                    f = False
                End If

            Next c

            'Define z
            z = MaxRange

            'Were all criteria satisfied?
            If f Then
                k = k + 1
                ReDim Preserve w(k)
                w(k) = z(i, 1)
            End If

        Next i

        MaxIfs = Application.Max(w)

        Exit Function
        ErrHandler:
        MaxIfs = CVErr(xlErrValue)

    End Function

This code allows 1 to multiple conditions.

This code was developed with reference to multiple code posted by Hans V over at Eileen's Lounge.

Happy coding

Diedrich