# maxifs 2010 min - Excel MAXIF function or emulation?

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

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?

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

There are lots of ways to accomplish this - The one I came up with used array formulas. Type this in:

```
=MAX((--(A:A=A1))*(B:B))
```

And then hit `Ctrl`

+`Shift`

+`Enter`

to make it an array formula

Hope this does the trick