Excel MAXIF function or emulation?

Question

I have a moderate sized dataset in excel from which I want to extract the maximum value of the values ​​in column B, but those that match only the cells in column A that satisfy certain criteria.

The desired functionality is similar to the SUMIFor function COUNTIF, but none of this return data is required. No function MAXIF; how do i imitate one?

Answer 1

You can use an array formula. In the cell in which you want to get the maximum calculation, enter: = Max (If ([test], [if true], [if false]), where you replace the values ​​in square brackets with the help of 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. Please note that I use the range of cells in my comparison and the value if false, rather than a single cell.

Now, continuing to edit the cell, press Ctrl + Shift + Enter (hold the Ctrl and Shift keys together, then press Enter).

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

EDIT By the way, did you want to do this programmatically or manually? If programmatically, what environment are you using? VBA? WITH#?

EDIT. VBA FormulaArray R1C1, :

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

, (, " , , , ). , VBA, - :

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

, . , MaxIfs. :

        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 was developed with reference to several codes posted by Hans V in the Eileen Lounge.

Happy coding

Didrich