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
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:
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.