MAX IF in Excel to get highest value with conditions

The article shows a few different ways to get the max value in Excel based on one or several conditions that you specify.

In our previous tutorial, we looked at the common uses of the MAX function which is designed to return the largest number in a dataset. In some situations, however, you may need to drill down into your data further to find the max value based on certain criteria. This can be done by using a few different formulas, and this article explains all possible ways.

Excel MAX IF formula

Until recently, Microsoft Excel did not have a built-in MAX IF function to get the maximum value based on conditions. With the introduction of MAXIFS in Excel 2019, we can do conditional max an easy way.

In Excel 2016 and earlier versions, you still have to create your own array formula by combining the MAX function with an IF statement:

{=MAX(IF(criteria_range=criteria, max_range))}

To see how this generic MAX IF formula works on real data, please consider the following example. Supposing, you have a table with the long jump results of several students. The table includes the data for three rounds, and you are looking for the best result of a particular athlete, say Jacob. With the student names in A2:A10 and distances in C2:C10, the formula takes this shape:

=MAX(IF(A2:A10="Jacob", C2:C10))

Please keep in mind that an array formula must always be entered by pressing the Ctrl + Shift + Enter keys simultaneously. As the result, it is automatically surrounded with curly brackets like shown in the screenshot below (typing the braces manually won't work!).

I real-life worksheets, it's more convenient to input the criterion in some cell, so that you can easily change the condition without changing the formula. So, we type the desired name in F1 and get the following result:

=MAX(IF(A2:A10=F1, C2:C10))
Excel MAX IF formula to find the highest value with condition

How this formula works

In the logical test of the IF function, we compare the list of names (A2:A10) with the target name (F1). The result of this operation is an array of TRUE and FALSE, where the TRUE values represent names that match the target name (Jacob):

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

For the value_ if_true argument, we supply the long jump results (C2:C10), so if the logical test evaluates to TRUE, the corresponding number from column C is returned. The value_ if_false argument is omitted, meaning will just have a FALSE value where the condition is not met:

{FALSE;FALSE;FALSE;5.48;5.42;5.57;FALSE;FALSE;FALSE}

This array is fed to the MAX function, which returns the maximum number ignoring the FALSE values.

Tip. To see the internal arrays discussed above, select the corresponding part of the formula in your worksheet and press the F9 key. To exit the formula evaluation mode, press the Esc key.

MAX IF formula with multiple criteria

In situation when you need to find the max value based on more than one condition, you can either:
Use nested IF statements to include additional criteria:

{=MAX(IF(criteria_range1=criteria1, IF(criteria_range2=criteria2, max_range)))}

Or handle multiple criteria by using the multiplication operation:

{=MAX(IF((criteria_range1=criteria1) * (criteria_range2=criteria2), max_range))}

Let's say you have the results of boys and girls in a single table and you wish to find the longest jump among girls in round 3. To have it done, we enter the first criterion (female) in G1, the second criterion (3) in G2, and use the following formulas to work out the max value:

=MAX(IF(B2:B16=G1, IF(C2:C16=G2, D2:D16)))

=MAX(IF((B2:B16=G1)*(C2:C16=G2), D2:D16))

Since both are array formulas, please remember to press Ctrl + Shift + Enter to complete them correctly.

As shown in the screenshot below, the formulas produce the same result, so which one to use is a matter of your personal preference. For me, the formula with the Boolean logic is easier to read and build – it allows adding as many conditions as you want without nesting additional IF functions.
MAX IF formula to get the largest number with multiple criteria

How these formulas work

The first formula uses two nested IF functions to evaluate two criteria. In the logical test of the first IF statement, we compare the values in the Gender column (B2:B16) with the criterion in G1 ("Female"). The result is an array of TRUE and FALSE values where TRUE represents data that match the criterion:

{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE}

In a similar fashion, the second IF function checks the values in the Round column (C2:C16) against the criterion in G2.

For the value_if_true argument in the second IF statement, we supply the long jump results (D2:D16), and this way we get the items that have TRUE in the first two arrays in corresponding positions (i.e. the items where the gender is "female" and round is 3):

{FALSE; FALSE; FALSE; FALSE; FALSE; 4.63; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 4.52}

This final array goes to the MAX function and it returns the largest number.

The second formula evaluates the same conditions within a single logical test and the multiplication operation works like the AND operator:

When the TRUE and FALSE values are used in any arithmetic operation, they are converted into 1's and 0's, respectively. And because multiplying by 0 always gives zero, the resulting array has 1 only when all the conditions are TRUE. This array is evaluated in the logical test of the IF function, which returns the distances corresponding to the 1 (TRUE) elements.

MAX IF without array

Many Excel users, including me, are prejudiced against array formulas and try to get rid of them wherever possible. Luckily, Microsoft Excel has a few functions that handle array natively, and we can use one of such functions, namely SUMPRODUCT, as kind of "wrapper" around MAX.

The generic MAX IF formula without array is as follows:

=SUMPRODUCT(MAX((criteria_range1=criteria1) * (criteria_range2=criteria2) * max_range))

Naturally, you can add more range/criteria pairs if needed.

To see the formula in action, we will be using the data from the previous example. The aim is to get the maximum jump of a female athlete in round 3:

=SUMPRODUCT(MAX(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))

This formula is competed with a normal Enter keystroke and returns the same result as the array MAX IF formula:
A non-array MAX IF formula in Excel

Casting a closer look at the above screenshot, you can notice that invalid jumps marked with "x" in the previous examples now have 0 values in rows 3, 11 and 15, and the next section explains why.

How this formula works

As with the MAX IF formula, we evaluate two criteria by comparing each value in the Gender (B2:B16) and Round (C2:C16) columns with the criteria in cells G1 and G2. The result are two arrays of TRUE and FALSE values. Multiplying the arrays' elements in the same positions converts TRUE and FALSE into 1 and 0, respectively, where 1 represents the items that meet both criteria. The third multiplied array contains the long jump results (D2:D16). And because multiplying by 0 gives zero, only the items that have 1 (TRUE) in the corresponding positions survive:

{0; 0; 0; 0; 0; 4.63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52}

In case max_range contains any text value, the multiplication operation returns the #VALUE error because of which the entire formula won't work.

The MAX function takes it from here and returns the largest number that meets the specified conditions. The resulting array consisting of a single element {4.63} goes to the SUMPRODUCT function and it outputs the max number in a cell.

Note. Because of its specific logic, the formula works with the following caveats:

  • The range where you search for the highest value must contain only numbers. If there are any text values, a #VALUE! error is returned.
  • The formula cannot evaluate the "not equal to zero" condition in a negative data set. To find max value ignoring zeros, use either a MAX IF formula or MAXIFS function.

Excel MAX IF formula with OR logic

To find the max value when any of the specified conditions is met, use the already familiar array MAX IF formula with the Boolean logic, but add the conditions instead of multiplying them.

{=MAX(IF((criteria_range1=criteria1) + (criteria_range2=criteria2), max_range))}

Alternatively, you can use the following non-array formula:

=SUMPRODUCT(MAX(((criteria_range1=criteria1) + (criteria_range2=criteria2)) * max_range))

As an example, let's work out the best result in rounds 2 and 3. Please pay attention that in the Excel language, the task is formulated differently: return the max value if round is either 2 or 3.

With the rounds listed in B2:B10, the results in C2:C10 and criteria in F1 and H1, the formula goes as follows:

=MAX(IF((B2:B10=F1) + (B2:B10=H1), C2:C10))

Enter the formula by pressing the Ctrl + Shift + Enter key combination and you will get this result:
An array MAX IF formula with the OR logic

The max value with the same conditions can also be found by using this non-array formula:

=SUMPRODUCT(MAX(((B2:B10=F1) + (B2:B10=H1)) * C2:C10))

However, we need to replace all "x" values in column C with zeros in this case because SUMPRODUCT MAX only works with numeric data:
A non-array MAX IF formula with the OR logic

How these formulas work

The array formula works exactly the same way as MAX IF with AND logic except that you join the criteria by using the addition operation instead of multiplication. In array formulas, addition works as the OR operator:

Adding up two arrays of TRUE and FALSE (which result from checking the values in B2:B10 against the criteria in F1 and H1) produces an array of 1's and 0's where 1 represents the items for which either condition is TRUE and 0 represents the items for which both conditions are FALSE. As the result, the IF function "keeps" all the items in C2:C10 (value_if_true) for which any condition is TRUE (1); the remaining items are replaced with FALSE because the value_if_false argument is not specified.

The non-array formula works in a similar manner. The difference is that instead of IF's logical test, you multiply the elements of the 1's and 0's array by the elements of the long jump results array (C2:C10) in the corresponding positions. This nullifies the items that do not meet any condition (have 0 in the first array) and keeps the items that meet one of the conditions (have 1 in the first array).

MAXIFS – easy way to find highest value with conditions

The users of Excel 2019, 2021 and Excel 365 are free from the trouble of taming arrays to build their own MAX IF formula. These versions of Excel provide the long-awaited MAXIFS function that makes finding the largest value with conditions child's play.

In the first argument of MAXIFS, you enter the range in which the maximum value should be found (D2:D16 in our case), and in the subsequent arguments you can enter up to 126 range/criteria pairs. For example:

=MAXIFS(D2:D16, B2:B16, G1, C2:C16, G2)

As shown in the screenshot below, this simple formula has no problem with processing the range that contains both numeric and text values:
Excel MAXIFS function to find the highest value with conditions

For the detailed information about this function, please see Excel MAXIFS function with formula examples.

That's how you can find max value with conditions in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel MAX IF formula examples (.xlsx file)

Table of contents

130 comments

  1. I found it very useful. thank you so much.

  2. Dear Sir,

    Please advise,

    I want to calculate, total amount A1 (Amount is 5396) and A2 (Amount is 5087), then total is 10,483 will come from this value 12% to be calculated, then Rs.1257.96 will come.
    then i want formula if 12% calculated amount is comes above Rs.1800 , to be show Rs.1800 (or) incase if below 1800 means comes, to be show actual amount Rs.1257.96.

    Thanks
    D.Arul

    • Hi! To show the smallest number, use the MIN function. For example:

      =MIN((A1+A2)*0.12,1800)

      You can also use the IF function to get a value by condition.

      =IF((A1+A2)*0.12>1800,1800,(A1+A2)*0.12)

  3. Hi

    This is the data i am having

    A 2 January 2024
    A 5 December 2023
    A 11 December 2023
    A 11 December 2023
    A 11 December 2023
    A 6 February 2024
    B 11 January 2024
    B 11 January 2024
    B 12 January 2024
    C 12 January 2024
    C 7 December 2023
    C 31 January 2024
    C 1 February 2024

    and the desire result would be :

    A 6 February 2024
    B 12 January 2024
    C 1 February 2024

    Can you please help me with the formula to do so

    • Hi! If there is some relationship between your data and the desired result, then explain that relationship. Also explain how many columns your data occupies one two or three. I don't want to guess.

      • So in total I have 2 columns in excel, Column A has data in regards to name of a person and column B has data about the login date of that person in the system for multiple days. I need the output with names in one column and the latest date of login in the system for that person.

  4. Hello! I am trying to get a column to auto-number in order whenever a row is added.

    I will need to frequently add new actions into a list along the lines of the below example. They won't be in order when added into the list, but I want to ultimately be able to sort by the "Action #" column.

    I won't always be able to see the other columns to be able to add the action # manually, so I'd like to have the action number automatically update to be 0.1 higher than previous highest action number for that topic.

    Any tips? Many thanks in advance!

    Topic # Topic Action # Action details
    16 Fruit 16.1 …
    16 Fruit 16.3 …
    16 Fruit 16.4 …
    16 Fruit 16.2 …
    17 Water 17.2 …
    17 Water 17.1 …
    16 Fruit 16.5 …

  5. Thank you

  6. Need a formula that gives the date on which the maximum units were sold as per the highest, second highest and so on. and the corresponding units sold

  7. I'm trying to combine IF, FILTER, and MAXIF to essentially get the sum of all numbers (Column B) for each particular group (Column A) below the indicated threshold, taking the largest numbers first. At the same time however, I am wanting to return a simple "Yes" or "No" in Column D as to which numbers (Column B) should be included and summed together for each particular group to be below the threshold.

    Although I considered embedding my IF formula with a subtraction of simple LARGE formulas for each data set, there is variable amount of group names and associated numbers, making it difficult and time consuming to determine how many different times I would have to repeat the LARGE function. In turn I figured it would make sense to potentially embed the MAXIF formula

    If you have any insight on how I could use MaxIF(s) to accomplish my goal, please let me know.

    I have provided a sample data set below as an example witht he following specific question

    How can I determine which items (with a yes or no) from Column B (number), specific to their criteria in Column A (group name), need to be added together to get below the indicated threshold in column C. taking the largest numbers first for each group.

    Group Name - Column A

    Group 1
    Group 1
    Group 1
    Group 1
    Group 2
    Group 2
    Group 2
    Group 2
    Group 2

    Number - Column B

    100
    347
    532
    431
    2500
    37890
    4766
    5467
    2345

    Threshold - Column C
    450
    450
    450
    450
    3000
    3000
    3000
    3000

      • Hi - I appreciate your response.
        I don't believe that the formula above is what I am looking for.. In using the formula above - I am returned the sum product of 532 for Group 1 - which leaves a remaining amount associated with Group 1 above the threshold of 450 (100 +347 +431) > 450.

        Furthermore - I am trying to determine with a yes or no which numbers corresponding to group 1 would need to be included together to sum below the threshold rather than the numerical value (sum of column B) that achieves the the threshold amount - am I able to do this by embedding by embedding the sum prodcut formula with an if?

        In all - I am looking to return in column D with my formula the following results for Group 1 as an example

        Column D
        No
        No
        Yes
        Yes

        In this result the amounts of 532 and 431 are the only numbers needed to add together to reduce the population amount of Group 1 below the threshold of 450 for Group 1 as the remaining numbers associated are only 447 (347+100)

        Let me know if this information is helpful and if you have any other questions surrounding my problem. Thanks!

  8. I'm looking to possibly combine FILTER and UNIQUE to return every row that has a maximum in Column B for each unique value of Column A and every row that has a maximum in Column C for each unique value of Column A.

    I was trying some version of the following formula, aiming for the max of Column B first, then adding in Column C once I got it working. However I still haven't got it working so I haven't tried adding in Column C yet.

    =FILTER(A1:C13,B1:B13=MAXIFS(B1:B13,A1:A13,UNIQUE(A1:A13)))

    Given:
    A 3.54 0.39
    A -6.04 1.02
    A 6.16 0.79
    A 6.16 0.86
    B 18.33 0.92
    B 4.99 0.85
    B 12.26 0.4
    B 5.28 0.33
    B 15.22 -6.7
    C 5.55 0.59
    C 1.36 0.94
    C -10.81 -4.84
    C -4.63 -6.99

    Desired Result:
    A 6.16 0.79
    A 6.16 0.86
    A -6.04 1.02
    B 18.33 0.92
    C 5.55 0.59
    C 1.36 0.94

    (I typed all these values manually, so I hope I didn't mistype my desired result.)

    I was also thinking maybe some form of INDEX+MATCH using multiple criteria and a column = 0 in the INDEX function to return entire rows.

    If you could provide any insight, I would be very grateful. Thank you!

    • Hello! You can get the maximum values you want for "A" with this formula:

      =FILTER(A1:C13, (B1:B13=MAX(FILTER(B1:B13,A1:A13="A"))) + (C1:C13=MAX(FILTER(C1:C13,A1:A13="A"))))

      • That's pretty close, but I was really hoping not to have to hard-code "A", "B", etc... This is going to be used to filter software results output and the labels in Column A are just placeholders. In reality, I have no way of knowing ahead of time what those labels will be. My goal is to find all the unique values in Column A using UNIQUE (or something similar) and then find the max of columns B and C based on that list of unique labels.

        Maybe HSTACK or VSTACK could help? I'm not sure, I'm really new to these formulas and still learning.

        I might just have to make something using VBA. I was excited when I found out about the dynamic array functions because I thought I could accomplish what I need with just formulas.

        • Hi! You can create a list of unique values using the UNIQUE function. Then make references in this formula to each of these values.
          Your problem can be solved with the recursive function LAMBDA. Here is a detailed instruction: How to write recursive LAMBDA function in Excel with examples.
          Creating such functions, as well as VBA code, is beyond the help we provide on this blog. But I will try to think about your problem if I have some free time.

          • Oh, yeah, no worries about any VBA. That's on me.

            I've never used LAMBDA functions before, but I do know that they exist and have seen them in passing. I will investigate further. Thank you for the suggestion.

      • 114 315 116 211 118 5 81 199 122
        this is the data on A22:I21 which is the sum of daily expenses. i wanted to highlight all the time the highest number below 194. (Ignore all the number above 194 all the time), i have used the below formula, but still not highlighting the
        AND(A22:I22<=194, $A22:$I22=MAX($A22:$I22)), this formula supposed to highlight 122 as this is the highest number under 194

        • Please re-check the article above since it covers your task. Use MAXIFS function as described above:

          =MAXIFS(A22:I22,A22:I22,"<194")

          Or use MAX IF:

          =MAX(IF(A22:I22<194,A22:I22))

  9. Hi there, please help, I am encountering an issue to get my desired output.

    I have a very large set of data, and and a lot of them are duplicates. For each set of duplicates, how do I only return the highest value of rating? Also, if the set of duplicates contains the same high rating, it will only return 1 value for each duplicates although there are multiple high rating with same value.

    Example:

    *Data* *Rating*
    DATA1 2
    DATA1 1
    DATA1 3
    DATA2 1
    DATA2 2
    DATA3 3
    DATA3 2
    DATA3 3

    Desired output would be:

    *Data* *Rating*
    DATA1
    DATA1
    DATA1 3
    DATA2
    DATA2 2
    DATA3 3
    DATA3
    DATA3

    I have trying for almost 2 weeks but I just can't seem to figure it out. Please help!!! Thank you

  10. In column a having multiple common number and column b having lowest to largest number for each common number than how to identify each common number last 4 highest number

    • Your task is not completely clear to me. To understand what you want to do, give an example of the source data and the desired result.

  11. How will i get preferred supplier name from below table for maximum value?

    Item Supplier1(Name) Supplier1(Value) Supplier2(Name) Supplier2(Value) Supplier3(Name) Supplier3(Value) Preferred Supplier name (max value)
    12345 Andrew 1000 Thomas 5000 Phillip 3000 ??????

    • hello.
      i am trying to writing formula like.
      i have a name Albert in cell "g1", and i have a data in column A column B and Column c
      i am writing the formula in h1 column
      Column A = Name of customer
      Column B = Phone number of customer
      Column C.= Date of sale

      now in column A each customer coming many time
      column b mobile number of customer( note here: number is different but name can be same)

      now i want to find the the last date of sell from column C.

      and also want to find the first date of sale

      hope you understand what i mean. i am actually not very good in english.

      thank you

        • i am close but not actually what i want. can i show you my data if this possible. it might me help to get exact what i actually want.

  12. hı am tryıng to get an average for 3 best quızzes usıng ıf functıon (there are fıve quızzes rıght e.g
    name q1 q2 q3 q4 q5
    hassan 65 87 34 90 68

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)