Comments on: MAX IF in Excel to get highest value with conditions

Almost every Excel user is familiar with the good old MAX function that is designed to return the largest value 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. Continue reading →

Comments page 2. Total comments: 90

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

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

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

    Thanks

    1. Hello!
      This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription.

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

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

  6. 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,"<>")

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

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

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

  10. Svetlana!!
    One Caveat: Your all articles are very attractive. its consume my time eventhough i have learnt many..

    1. Hi Imran,

      If that's the only caveat, I think I can live with it :) And thank you for your comment, it really put a smile on my face.

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