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

Posted on 2012-09-15
Last Modified: 2012-09-22

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 ?

Question by:Steve_Brady
    LVL 26

    Accepted Solution

    Hi, Steve.

    The following is an array-entered formula (Control-Shift-Enter). Please let me know if you need more on this.

    LVL 11

    Expert Comment

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

    LVL 24

    Expert Comment

    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?

    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

    LVL 26

    Expert Comment

    Thanks, Steve.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now