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

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

  2. Hi All,

    This platform look very amazing.
    I need help with identifying the right formula for an array.
    I have a table like this:

    Task 18/05/2022 19/05/2022 20/05/2022 21/05/2022 22/05/2022
    1 1
    2 4
    3 2
    4 6
    5 3
    6 5

    how would I search for the last entry made in this array and return the corresponding date, as the task are done in the sequential order.

    1. Sorry It looks confusing, I thought it'll read blanks but it didn't.

      task 1 was done on 18/05
      task 2 was done on 20/05
      task 3 & 4 was done 19/05
      task 5 was done on 22/05
      task 6 was done on 21/05

        1. HI Alex,
          thanks for the tip and it does help in a way. But I need a single formula that picks last non empty cell in multiple columns and rows, like you know searching through task1 to task6 and return the last date irrespective of the task name as reference.

          I really appreciate your help

          1. Hi!
            The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
            You can find the address of the last cell with data in the table using a VBA macro.

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

  4. A B C D
    Expenses Remaining Balance Limit
    1 John 1000 9000.00 (he gets 1000) 10000
    2 Mary 1500
    3 Julie 2000
    4 John 2500 6500 (he gets 2500)
    5 John 1500 5000 (he gets 1500)
    6. John 7000 5000 (he have to get 5000 only because his limit is 10000.)

    The limit of each is Rs 10000.00. Please let me help to calculate the expenses of john not exceeding to Rs 10000.00.
    =IF(B1<D1,B1,(SUMIF(A1:A6,A1,B1:B6)- D1))
    5000 Should be resulted to be paid to John against his expenses not to exceed 10000

  5. I have a table of chemical data. The main identifier is CAS#. I want to use the criteria of MAXIFS to sort out data flags, E and U AND I want to find the data value that has the latest sample date.

    A B C D E F
    CAS Agent Conc UOM Flags Sample Date
    1 100-41-4 Ethyl Benzene 0.002 ppm 2/2/2016
    2 100-41-4 Ethyl Benzene 0.002 ppm 1/27/2016
    3 100-41-4 Ethyl Benzene 0.005 ppm 1/27/2016
    4 100-41-4 Ethyl Benzene 0.002 ppm U 1/27/2016
    5 100-41-4 Ethyl Benzene 0.002 ppm 2/2/2016
    6 100-41-4 Ethyl Benzene 0.012 ppm E 2/3/2016
    7 100-41-4 Ethyl Benzene 0.002 ppm 2/2/2016
    8 100-41-4 Ethyl Benzene 0.002 ppm 2/10/2016
    9 100-41-4 Ethyl Benzene 0.002 ppm Y 2/10/2016

      1. Thank you, that is pretty close, but not specific enough. Lets try this. I want a function that would return the values of 123 = 3, 987 = 7. The ID# is the unique identifier for each sample, need the most recent, largest value for each ID.

        Column/Row A B C
        1 ID# Value Date
        2 123 5 2021-12-24
        3 123 2 2022-03-25
        4 123 3 2022-03-25
        5 987 6 2021-12-24
        6 987 7 2022-03-15

        1. Hi!
          The answer you received earlier is consistent with the information you provided. If you need to get only one value, you can use the INDEX+MATCH functions.
          Replace the ID# number (987) in this formula with a reference to the desired cell.

          =INDEX(B2:B6,MATCH(1, (MAX((A2:A6=987)*C2:C6)=C2:C6)* (MAX((MAX((A2:A6=987)*C2:C6)=C2:C6)*B2:B6)=B2:B6),0))

          Here is the article that may be helpful to you: Excel INDEX MATCH with multiple criteria.

  6. I have bitcoin daily data from year 2011 to 2022. I am trying to find the max price for a specific year, but when I use this formula =(MAX(IF(YEAR($A:$A=Q2),$C:$C,0)))
    Column A has the dates
    Column C has the prices
    Column Q has the year eg. 2011

    When I put the formula it shows value error. Is there anything I am doing wrong?

    1. Hello!
      To find the maximum value for a specific year, I recommend using the MAXIF function. All the necessary information is in the article above.

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

  8. Hi,

    I have a list of dates and I have already found the formula for finding the latest date. But I would also like to have another condition where in another column if the value is not equalt to "ABC", then to exclude/skip it from the criteria.

    1. Hi!
      Have you tried the ways described in this blog post? Please re-check the article above since it covers your case.

  9. We have 2 columns one is size and other is quantity. We want both that the multiples of the quantity are close to the maximum of a certain value

    Like as
    4600 4600
    S,. No. Pcs Length (mm) Bar 1 Bar 2
    1 312 2256 2
    2 520 1506
    3 208 1831
    21 104 1506
    31 312 2256 2
    32 520 2256
    33 208 2256
    51 104 1356
    Total 58 58
    Stock 156 156

  10. Hello!

    If i would like to know if its possible to do such search and look if the value is among the, say top 80% of the values. Something like the conditional formatting, but in an if statement (if that makes any sense :D ).

    IF
    MAXIFS(D2:D16, B2:B16, G1, C2:C16, G2)... is ...
    among the top 80% of the values in the search criterias then print:

    "this jump is within the top 80% of all jumps by females in round 3"

    //Johan

    1. Hello!
      If I understand your task correctly, try the following formula:

      =IF((RANK.EQ(A2,$A$2:$A$11,0)/COUNT($A$2:$A$11)) < = 0.8,"top 80%","")

      You can learn more about RANK.EQ function in Excel in this article on our blog.

      1. Hello!

        Thansk alot, that's exactly what i was looking for.
        Although, i found PERCENTRANK.EXC( ). This seems to work as well.

        //Johan

        1. Hello!

          If i would like to make a similar solution but with instead use a weighted % value?
          SAy,. if i have a total of 1200 points.

          With the following distribution:
          A: 543
          B: 344
          C: 100
          D:90
          E:50
          F:22
          G:14
          H: 4
          I: 1

          A has 45,25% of the volume, B has 28,7% of the volume and C has 8,4% of the volume.
          All these holds 82,35% and would be "top 80%"
          Then D-G would be the next 15% and it would leave H and I in the bottom 5%.
          Is there a way to get this info in any formula?

          Kind regards
          Johan

            1. Hi!

              The thing is that i want to show this per row, something like an ABC grouping.

              It could be done with descending sorting and just take total sum (0.8*1200=960, below that would be the "breaking point" to not be within the top 80%. But i want to show this per cell, without having to make a descending sorting and a fixed number of rows.

              So something like:
              if A, 543/1200 is among top 80% of the values --> "top 80% [True]
              if B, 344/1200 is among top 80% of the values --> "top 80% [True]
              if C, 100/1200 is among top 80% of the values --> "top 80% [False]
              if D, 90/1200 is among top 80% of the values --> "top 80% [False]
              and so on...

              Top 2 values would hold 45,25+28,7 = 73,95% of the volume.
              The next value will hold 8,4% of the volume and that would then be within the top 20%.

              Im not sure if this is possible? I thought that the percentagerank.exk would do the trick, but this only returns the top 80 % of the values, not taking the actual weight on each row into the calculation.

              Sorry if it got a little fuzzy... :D

              Kind regards Johan

              1. Hi!
                Data in ABC grouping should always be sorted in descending order.
                Try this formula:

                =IF(OR(AND(SUM($A1:A$2)/SUM($A$2:$A$11)<0.8, SUM($A$2:A2)/SUM($A$2:$A$11)>0.8), SUM($A$2:A2)/SUM($A$2:$A$11)<0.8), "top 80% ","")

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

  12. What is the formula to get min or max data shown below. Note dates with annex "A". Thank you
    10-Oct-20 A
    12-Oct-20 A
    15-Oct-20 A
    20-Oct-20 A
    25-Oct-20 A

  13. Great.. It works perfectly, the only issue is that the excel gets little slow but still I got what I was looking for. Thanks a lot..

  14. I need to find second highest number in an array using logical not direct formula (large or small) that I know, please help:

    Name Number
    A 10
    B 20
    C 30
    D 40
    E 50
    F 60

    Like 50 is the second highest number in the above array but I need to find it with logic.

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

  16. Thank you

  17. It helps me. Thank you

  18. YOU MAKE MY DAY, THANKS

  19. I have not been able to find anything that addresses Max with one condition - where the target is not found. The Max condition is returning 0, as it should, but how do I change that 0 into a blank in the result cell.
    My formula:
    {=IFERROR(IF('Rep Visit Recap'!$K25>"",MAX(IF('Rep Visit Recap'!$K25='Checkin Data'!$P$2:$P$3815,'Checkin Data'!$A$2:$A$3815,"")),""),"")}
    It's looking for a the last date if the account number is found. It works great except when there are no rows for that account number (I can't know this ahead of time, the checkin data are loaded into the workbook from another application).
    How can I make the result date a blank, instead of a 0 when no rows are found for the account? I'm sure it's a simple think but I can't find any guidance for this.

  20. Hi
    I have a what is probably a silly question. I would like to maintain a record of a minimum value in a cell as the spreadsheet is updated through time. This requires an if statement to compare the current known low with the updated value. If the new update is below the current low then an update step should replace the current all time low with the new all time low. In my trials the circular nature of this algorithm causes issues.
    Thanks

  21. Hi Svetlana,
    This article was really, really helpful! Thank you so much for walking through how this formula can be used in various combinations in such detail.

    I'm currently working on a problem and am using one of these conditional functions to solve it, but it's giving me some unexpected results and for the life of me I can't figure out why it's going wrong.

    I need to work out the MAX and MIN values for various samples but I want to exclude smaller data points - this is my criteria. I have to do this for 60 samples of varying size so I want to apply a consistent threshold across them all - I've decided to exclude data points that are below half the average for each sample.

    The issue seems to be, when combining with INDEX MATCH I also need to apply the same criteria to the MATCH lookup array, otherwise it could return a result of one of the excluded data points that has the same max value (which in this case is a percentage so is a strong possibility).

    This is the formula I'm playing with:
    =INDEX(Full_sample[Country],MATCH(MAX(IF(Full_sample[# Requested]>(AVERAGE(Full_sample[# Requested])/2),Full_sample[Country Response rate])),((Full_sample[# Requested]>(AVERAGE(Full_sample[# Requested])/2))*(Full_sample[Country Response rate])),0))

    Currently this formula indexes a country whose sample size is only 1, when half the average of the full sample is 12. When I break the formula down into constituent parts the MAX formula seems to work fine so I believe the issue lies with the MATCH lookup array and adding the AVERAGE calculation within it. The syntax of telling it to only look at response rates where the # requested > half the average seems correct, but I don't think it likes having that additional formula within the lookup array?
    I've tried incorporating SUMPRODUCT within the MATCH and got slightly better results, but still incorrect. And strangely, when I alter the criteria to AVERAGE*2 or write in a hard number it seems to work correctly (maybe).

    I've spent hours trying to solve this but am really stuck. I would very much appreciate your advice on how to incorporate this kind of criteria into the MATCH lookup array to make sure it is only indexing the values that correspond with the MAX criteria.

    Thanks

    1. [UPDATE]

      I believe I have found a solution so wanted to post it before you spent any time on my above query. However, it seems a bit convoluted so I would still appreciate your advice on whether this is the best way to approach it or if you would recommend something else.

      I ended up creating another nested IF within the lookup array where the TRUE value = 1 and match that to a criteria of 1. So rather than starting with the MAX (or MIN) as my match criteria, I incorporated my MAX and MINs into my IF lookup array. So far this appears to give me the correct result.

      So the formula now is:
      =INDEX(Full_sample[Country],MATCH(1,IF(Full_sample[# Requested]>(AVERAGE(Full_sample[# Requested])/2),IF(Full_sample[Country Response rate]=MIN(Country_Full_sample[F HQ Country Response rate]),1)),0))

      I've used a MIN example here because it presents another issue, albeit one I'm prepared to live with. It could also happen in my MAX calculations but is less common.
      The above formula returns a country whose response rate was 0, but at least where it's sample size was above half the average of the population (>12), whereas previously it was returning a country whose response rate was also 0 but sample size was only 1. However, there are 3 countries above the requested threshold who have response rates of 0. Ideally, I would like the indexed result to return the country who had the largest sample above the threshold whose response rate was 0 (in this case). This would tell me which country performed more objectively worse (or better in MAX cases), as it would take into account both the absolute size of the sample and the response rate.

      Perhaps adding another ">" equation or incorporating the LARGE function may work, but I suspect this may make it more convoluted than is necessary. I've already set a minimum threshold so I could just as easily say that any country with the lowest response rate is the same as another.

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

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

  24. Could help me with this function I tried it many times and it doesn't work for me,I am using Exel 2013!
    it always shows me the maximum value in $F$7:$F$16
    Names Values "=MAX(IF($E$7:$E$16=E7,$F$7:$F$16))"
    Ahmed 1 88
    Ahmed 2 88
    Ahmed 66 88
    Tamer 1 88
    Tamer 77 88
    Khalid 1 88
    Khalid 2 88
    Rami 1 88
    Rami 2 88
    Rami 88 88

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail?
      Your data has 3 columns. Which ones do you use? What data is in E and F? What result do you get and what do you want to get?

      1. The team member names are in E and the the team member totals are in F. They are trying to find the highest total for each of the team members individually, but the max if formula is only returning the highest total in F, which is 88.

        I am having the same problem. I have have a list of 40k totals made up of 300 team members. I need to find the highest total for each individual team member, however the max if formula is giving me just the highest overall total in column F.

        Any advice on how to have it only grab the highest total for an individual team member?

        1. Hello!
          If you have Office 2019, or if you have a Microsoft 365 subscription, use the formula

          =MAXIFS(F:F,E:E,I1)

          where I1 - name of the member

          If you have an older version MS Office -

          {=MAX(IF(E:E=I1,F:F))}

          This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.
          I hope it’ll be helpful.

  25. Hi Svetlana,
    I'm very thankful for this site & excel knowledge as it has developed my excel skills.
    Now i want to know, how we can determine the top 3 Max values considering this MAX function??
    From this example how i would get second 'MAX JUMP' value? by formula..

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

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

  28. Hi..
    I have a table of data which showing list purchasing items in our stock from 2004 to date,we wanted to get the latest date of purchased for each stock, i used MAXIF function and it worked, now i want te get the lastest supplier used for each of our stock item..we have approximately 5000 items . I tried to use MAXif functions but it fails,does it work only with criterias which involve numbers only......?

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

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

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

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



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