asked on

What Excel command functions like =INCLUDEIF() (if such a command existed)?

Hello,

Is there a way to apply =MAX() to a single column range but only have it evaluate cells in the range which have a specific value in the adjacent column?

In other words, what function operates like =COUNTIF() or =SUMIF() but for something other than counting or summing — sort of like "=INCLUDEIF()" or in this specific case, "=MAXIF()."

For example, suppose all cells in A5:A1000 have a random integer value between 2005 & 2012. Is it possible to use =MAX(B5:B1000) but only have it evaluate those cells in B5:B1000 for which the value "2010" is present in the corresponding row in column A ?

Thanks

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Swapnil Nirmal

Attaches is the sample of VBA based approach

I have created a function (which is not array based) through which conditional MAX can be easily identified

Public Function MaxIF(criteriaRange As Range, _
searchValue As Variant, calcRange As Range)

Dim myCell As Range
Dim i As Integer

i = 0
MaxIF = 0

For Each myCell In criteriaRange
i = i + 1
If myCell.Value = searchValue Then
MaxIF = Application.Max(MaxIF, calcRange(i))
End If
Next myCell
End Function

Open in new window

Maxif-Function.xlsm

Steve

@n_swapnil...
1) what if all the matching values are negative? (MaxIF = 0 should be Application.Min?)
2) wouldn't it be faster to fill an array with all matching criteria and use just one Application.Max?

possibly:

Function MaxIF(CriteriaRange As Variant, SearchString As String, MaxRange As Variant) As Double
Dim TempArray() As Variant
Dim i As Long: i = 0
Dim j As Long: j = 1
For Each cell In CriteriaRange
    If cell.Value = SearchString Then
        ReDim Preserve TempArray(i)
        TempArray(i) = MaxRange(j, 1)
        i = i + 1
    End If
j = j + 1
Next cell

MaxIF = Application.Max(TempArray)

End Function

Open in new window

redmondb

Thanks, Steve.