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)

Latest comments

  1. Hi, i used the max if to fund the highest sales of my product, but what formula will i add if i want to know who is the buyer of the highest sales on my certain product.

    Thanks

  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

    1. 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. 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 …

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

  5. 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 ??????

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

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

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

  7. Hi all,

    quick question
    I have a table with similar data but different time

    Name Time
    A 10/31/2022 12:54:00
    B 10/27/2022 9:22:00
    A 10/26/2022 13:56:00
    B 10/21/2022 9:39:00
    C 10/20/2022 10:34:00
    D 10/19/2022 4:06:00

    How do i get to the earliest time for each name?
    I tried =MAX(IF(A$1:A$6=A1,B$1:B$6))
    But it give me the newest date of the all sheet and not newest date for Name A
    Thanks

  8. hi i would like to ask for correct formula for my following hypothetical problem:

    so i want to make a chart to measure...say, sport player performance. we have Player A that can score 5 points but usually either being tired or whatever will be benched after round 3 which then the second best player which is Player B will score 3 points consistently until he benched on round 5 which then the next best player of C through Z will tag in so fort and so on in diminishing return-esque fashion

    the result i was hoping is that as the formula is establish i can simply input the current round number and the excel will list the players with the highest score of the round along with their respective point. note that i also would like that it can display more than 1 set of result if multiple players of the given rounds have their points tied with each other

    obviously at least to my limited knowledge, i need to use a variant of Excel MAX IF formula... but i cant wrap my head of the correct string i need to input. i hope Ablebits experts can help me with this and it will be very much appriciated

  9. I have a worksheet for a list of classes with a max seating of 8 per class. I am trying to find the simplest formula to calculate the total enrolled and max class size to fill session columns, in order (1-4), and then be able to AutoFill adjacent columns afterwards, if possible.
    Example: TOTAL OF 4 SESSIONS
    Max Class Size in A5 = 8
    Total Enrolled in A6 = 27
    Session 1 in A7
    Session 2 in A8
    Session 3 in A9
    Session 4 in A10
    Any help you can provide will be greatly appreciated.

    1. Sorry for the confusion. I may have explained it wrong. Let me try again.

      Max Class Size in A5 is 8
      Total Enrolled in A6 is 27

      All sessions are a total maximum of 8, so if session 1 reaches 8 but the total enrolled is 27, then A8 would then be populated up to 8 before going to A9, etc. Does that make more sense? Not sure if this is a MAX IF function.

      I hope you can help. Thank you!

  10. I am trying to get the lasted date of an activity, against an entrant name ?
    In sheet 1 [summary] is a list of names (cells A2 to A49), cells B2 to B49 has a formula to count the number of activities for each entrant [ =COUNTIF(Activity!$D$2:$D$1037,A3) ] which works fine
    In sheet 2 [activity] contains entries for each name col A date, col B class, col C type, col D name
    On sheet 1 [summary] I am trying create a formula the get the last (highest) date for each name. I have tried " =MAX(IF(Activity!D2:D999=A3, Activity!A2:A999)) " but this returns 00/01/1900.
    Can anyone suggest where I am going wrong?

    1. Hello!
      I'm assuming that you entered the formula as normal, not as an array formula. Press Ctrl + Shift + Enter so that array function works.
      Also, you can use the MAXIFS function to find the maximum value with conditions.

      1. Thanks Alexander, that works a treat. I have not used the array function before. Best wishes Joe

  11. Sir
    total electricity units 336
    0-50 =7 rate
    0-100 =8.50
    101-300 =9.90
    301-500=10.40
    How to write if condition

  12. How to find the maximum value in one column but only if the number is integer.
    For example I have in my columns standard values 1; 1.1; 1.2; 1.3; 2; 2.1 ;2.2. I want the function to return me in the next below cell 3 = which is maximum if only integer taken into account? Thanks!

  13. What if you want to take this concept one step further and find the person who made the highest jump, but is male. So using two sets of criteria
    1. Male
    2. Highest Jump
    Return Value: Name

    1. Hi Solomon,

      Assuming the names are in column A, gender in column B, and the results in column D, this formula will work a treat:

      =INDEX($A$2:$A$16, MATCH(MAX(IF($B$2:$B$16="male", $D$2:$D$16)), IF($B$2:$B$16="male", $D$2:$D$16), 0))

      For the detailed explanation, please see How to find top values with criteria in Excel. The only difference is that this formula uses the MAX function to get the maximum value while the above linked example uses LARGE to extract top n values.

      In Excel 365, you can use the FILTER function in conjunction with MAX or LARGE for the same purpose: FILTER top n values with criteria.

  14. Hello Dear
    why ? My office can't do maxifs formula excel.
    please you help me.

    Thanks

  15. How about finding the 6 highest values in a whole table (both rows and columns)?

    1. Hi Yza,

      For this, you can use the LARGE function. Supposing your table is in A1:D20, enter the following formula in the topmost cell where you want the results to appear, and then drag it down through 5 more cells:

      =LARGE(A1:D20, ROWS(A$2:A2))

      The ROWS function is used to dynamically generate the k argument for LARGE, which determines the k-th highest value to return.

  16. Hi Svetlana;
    1 - I encounter, MAXIF is returning a zero date (00/00/1900) if the criteria not matched.
    Is it possible to get #N/A if the criteria not matched?
    2 - Is it possible to combine vlookup with MAXif?

    1. Hi Raham,

      1. That can be done by wrapping the IF function around your MAX IF formula and using the condition for the logical test. For example:
      =IF(A2:A10=F1, MAX(IF(A2:A10=F1, C2:C10)), "N/A")

      2. It is possible too. For example, the below formula finds the max value in A2:A10 based on condition (B2:B10=D2) and returns a match from column C:

      =VLOOKUP(MAX(IF(B2:B10=D2, A2:A10)), A2:C10, 3, FALSE)

      If you need to return values from left, then use INDEX MATCH instead of VLOOKUP as shown in this thread at stackoverflow.com.

  17. Hi,
    Thanks for this site which developed my basic formula skills and going on strongly.
    I need to define a formula to identify the max of selected column (A1:A10) with 2 criteria, (B1:B10) shall have specific word 'B' (in D5) and (C1:C10) shall not be empty.
    I've tried in 2 ways =MAXIFS(A1:A10,B1:B10,D5,C1:C10,""&"") and =MAX(IF((B1:B10=D5),(C1:C10""&""),A1:A10)) but both are showing error. Can you help me please?

    1. Hello Selva!
      If I understand your task correctly, the following formula should work for you:

      =MAXIFS(A1:A10,B1:B10,D1,C1:C10,"<>"&D2)

      where D2 is empty cell.

    2. Hi Selva,

      Just use "<>" as the second criteria (non-empty):
      =MAXIFS(A1:A10,B1:B10,D5,C1:C10,"<>")

  18. How would you find the highest result for each person?

    1. Hi Tamsin Jessica,

      Simply write a MAX IF formula for the first person as explained in this tutorial, and then copy the formula down by dragging the fill handle. Be sure to use a relative reference when referring to the cell containing the person's name for the reference to adjust correctly for each row.

      Alternatively, you can make a list of unique names, write a formula for the first person, and then drag the formula down to get the highest result for each person.

  19. The example only showing numbers. What if we want to reverse the result? IE from your example, who has the highest jump and on what round?

    1. Hi Jackie,

      For this, you can use the classic INDEX MATCH formula and nest a regular MAX function in the first argument of MATCH as the lookup value.

      Assuming column A contains names, column B - rounds and column C - results, the formulas would go as follows:

      The person who made the highest jump:
      =INDEX($A$2:$A$10, MATCH(MAX($C$2:$C$10), $C$2:$C$10, 0))

      On what round:
      =INDEX($B$2:$B$10, MATCH(MAX($C$2:$C$10), $C$2:$C$10, 0))

      1. Thanks, never thought of that. Ill try

        1. What if you want to take this concept one step further and find the person who made the highest jump, but is male. So using two sets of criteria
          1. Male
          2. Highest Jump
          Return Value: Name

          1. Hi Jenny,

            Assuming the names are in column A, gender in column B, and jump results in column D, you can use this formula:

            =INDEX($A$2:$A$16, MATCH(MAX(IF($B$2:$B$16="male", $D$2:$D$16)), IF($B$2:$B$16="male", $D$2:$D$16), 0))

            It is a simplified version of the formula to find top values with criteria. The difference is that here we use MAX IF to get the maximum value while the above linked example uses LARGE IF to filter top n values.

  20. How can I print max absolute value irrespective of sign (positive or negative) and print max with sign.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)