# Excel MAXIF function or emulation?

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

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?

## Finding a max value in a column based on a previous column in Excel

Courtesy of @Thomas please try:

```
=MAX(IF(B1:B100="FIS",C1:C100,))
```

with `Ctrl`+`Shift`+`Enter`, or adjust the range limits to suit.

## Finding maximum value in range containing same value

I suggest you delete ColumnC and start again - using your formula in C1. Then either drag C1 down as far as required or copy it and past it into C2:C whatever. But make sure the `=`

is inside the curly brace.