You are very much familiar with the Max function.
But what if there is a need to find max value from a range based on some conditions or logic. Max if function is a combination of If logic in Max.
There are many practical case scenarios where Max if function needed such as
Max mark scored by a student irrespective of subject
Maximum temperature of a city in summer (or any specified season) irrespective of year.
Here you will see four ways to apply or build Max If function in Excel
Using Maxifs function for Excel 2019 and above
If you have Excel 2019 version or Office 365 then there is an inbuilt function called Maxifs.
Maxifs function output max value from a range when specified condition is met.
In Maxifs function you can specify more than one condition.
In the below image you need to find out the maximum mark obtained for each subject based on left side table data.
Cell F5 Max If formula
=MAXIFS($C$3:$C$14,$B$3:$B$14,E5)
Note:- Maxifs is not available in Excel versions below Excel 2019. See the below method as an alternate of Maxifs.
Using array formula (Max and If function combo)
You can use Max and If functions to get the Max value based on a condition.
This max if is an array formula.
Cell F5 formula
=MAX(IF($B$3:$B$14=E5,$C$3:$C$14,0))
After writing the above formula you need to press CTRL+SHIFT+ENTER to make it an array formula.
Successful creation of an array formula is indicated by curly brackets “{}” around formula in formula bar.
In the below image , in formula bar you can curly brackets around formula {=formula}.
Working of Max if array formula
In an array setup IF($B$3:$B$14=E5,$C$3:$C$14,0) formula part outputs the below array for Hindi subject
{54;0;0;0;63;0;0;0;45;0;0;0}
To see this select if formula part then press F9 key.
After IF part evaluation formula looks like below for Hindi subject
=MAX({54;0;0;0;63;0;0;0;45;0;0;0})
Now Max function takes an array list as an input and outputs 63.
Suggested Read Quickly converting a range into array values
Video: Max if in Excel | How to make array formula in Excel (Hindi speech)
Max if formula without an array
Max if formula using an array formula can be complex and difficult to apply.
Here you will see how Sumproduct function can be used to get max value based on some condition.
You can also use this non-array formula to get max value based on multiple conditions.
Generic Syntax
=SUMPRODUCT(MAX((conditionRng1=value1)*(conditionRng2=value2)*maxValueRng))
Using Sumproduct function for max if
Cell F5 formula to get max value based on condition
=SUMPRODUCT(MAX(($B$3:$B$14=E5)*$C$3:$C$14))
The below image shows how you can use Sumproduct formula as an alternate of Maxifs inbuilt function in Excel 2019 and Office 365.
Cell G5 formula
=SUMPRODUCT(MAX(($A$3:$A$15=E5)*($B$3:$B$15=F5)*$C$3:$C$15))
Max If function using VBA UDF
In Excel 2019 and Office 365 there an inbuilt function for getting Max value based on conditions called Maxifs.
But you can make your own Max if function using VBA. This VBA function or UDF will work in all the Excel versions.
To make a custom Max if formula using VBA follow the below steps
Press ALT+F11 to view VBA IDE.
Create a new standard module. After creating a new standard module copy paste the below code section in the newly created module.
Learn more in detail: Create Excel VBA User Defined Function (UDF / custom function)
Function f_MaxIf(numValRng As Range, condRng As Range, _
val As String, Optional compareSign As String = "=")
Dim cel As Range, i As Long, conOk As Boolean
Dim mxVal As Variant, clmnCnt As Byte
i = 1
clmnCnt = condRng.Columns.Count
mxVal = 0
For Each cel In condRng
Select Case compareSign
Case "="
conOk = (LCase(cel.Value) = LCase(val))
Case "<>"
conOk = (LCase(cel.Value) <> LCase(val))
Case ">"
conOk = (LCase(cel.Value) > LCase(val))
Case "<"
conOk = (LCase(cel.Value) < LCase(val))
End Select
If conOk Then
If clmnCnt = 1 Then
mxVal = Application.WorksheetFunction.Max(numValRng.Cells(i, 1).Value, mxVal)
Else
mxVal = Application.WorksheetFunction.Max(numValRng.Cells(1, i).Value, mxVal)
End If
End If
i = i + 1
Next cel
f_MaxIf = mxVal
End Function
f_MaxIf function takes 4 inputs and 4th one is optional.
Cell F5 formula
=f_MaxIf($C$3:$C$14,$B$3:$B$14,E5)
1st input is number range from which you want get max value, 2nd is condition range, 3rd is for which value you want to test
4th is optional (“=”,”<>”,”>”,”<“) which is comparison operator, default is “=”
You can read F5 formula as give max mark value where subject is Hindi.
Cell G5 formula
=f_MaxIf($C$3:$C$14,$B$3:$B$14,E5,”<>”)
You can read G5 formula as give max mark value where subject is not Hindi.
“>” and “<” use in Max if function
Cell C4 formula (Max value, if number is greater than 60)
=f_MaxIf(A3:A14,A3:A14,60,”>”)
Cell C8 formula (Max value, if number is less than 60)
=f_MaxIf(A3:A14,A3:A14,60,”<“)
Suggested Readings
How to do reverse string search (find) in Excel?
How to run macro in Excel|Create macro button
How to Assign Serial Number to Duplicate or Unique Values In Excel?