Excel MAXIF function or emulation?


Answers

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




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.




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.




Links



Tags