is there a max if function in excel

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.

Maxifs function in Excel

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}.

Excel Max if array 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.

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))

Excel Max if formula without an array

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))

Excel max if formula without array with multiple conditions

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)

creating standard module in VBA IDE

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.

Excel Max if formula using VBA