Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-09-15
Medium Priority
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
  • 2
LVL 26

Accepted Solution

redmondb earned 2000 total points
ID: 38402497
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
ID: 38402975
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

ID: 38403556
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

ID: 38424953
Thanks, Steve.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 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