Learn how to a build a cloud-first strategyRegister Now

x
Solved

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

Posted on 2012-09-15
Medium Priority
492 Views
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
0
• 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.
=MAX(IF(A:A=2010,B:B,""))

Regards,
Brian.
0

LVL 11

Expert Comment

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
``````
Maxif-Function.xlsm
0

LVL 24

Expert Comment

ID: 38403556
@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
``````
0

LVL 26

Expert Comment

ID: 38424953
Thanks, Steve.
0

## Featured Post

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.
###### Suggested Courses
Course of the Month20 days, 16 hours left to enroll