Finding MIN IF or MAX IF in Excel

When you were first learning how to use Excel, you quickly discovered the basic Excel functions, like:

In the olden days, the hardy Excel pioneers had to type those function names, to create a formula. That's why we look so tired! For example, cell F3 has a formula to find the lowest quantity sold:


To find the highest quantity sold, there is a MAX formula in cell G3:


Excel MIN function

Things are easier now – you can select the basic functions from the AutoSum drop down. Life is good!

AutoSum functions

Beyond the Basics with MIN IF

In this example, we want to see the MIN and MAX for a specific product. To make it easy to select a product, I created a drop down list of product names, by using data validation.

Excel drop down list

While MIN and MAX are useful if you want the overall highest and lowest amounts, what if you want to add criteria? There is a SUMIF function and a COUNTIF function, but no MINIF or MAXIF.

So, it won't be a snap to do a MIN with criteria, but we can muster that pioneer spirit, and create our own MINIF formula. To do this, we'll create a formula that combines the built-in MIN and IF functions. So, the formula starts with those two functions, and their opening brackets:


Next, we want to find the rows where the product name matches the product selected in the drop down list



And if the product name matches, we want to test its quantity



To finish the formula, type two closing brackets, and then press Ctrl+Shift+Enter to array-enter the formula.



When you look at the formula in the Formula Bar, there are curly brackets automatically added at the start and end of the formula, because it was array-entered.

If you don't see those curly brackets, you pressed Enter, instead of Ctrl + Shift + Enter. To fix it, click somewhere in the formula bar, and press Ctrl + Shift + Enter.


NOTE: If you plan to copy this formula down a column, use absolute references to the ranges:


Create a MAXIF Formula

To find the maximum quantity for a specific product, follow the same steps, using MAX instead of MIN.


And remember to press Ctrl+Shift+Enter


Congratulations! You are now a rugged Excel pioneer, building your worksheets with your bare hands, from rocks and tree stumps.

Watch the Video

To see the steps for creating MIN IF and MAX IF formulas, you can watch this short Excel video tutorial. The sample file for this video can be downloaded from the Contextures website, on the MIN and MAX page.



74 comments to Finding MIN IF or MAX IF in Excel

  • Ranjeet Kumr

    Min IF or Max IF is going to be very useful to me in the near future.

  • Gerald Strever

    Wow, that's very cool, Debra. Thank you!

  • Khvicha Gogichashvili

    I like it!
    can use in VBA:
    Range("D11?).FormulaArray = "=MIN(IF(C2:C8=C11,D2:D8))"

  • Darryn

    Thanks VERY much!!! I spent about 4 hours trying to work out how to take the highest number from a large list of numbers if the first three numbers were "x" and with the help of your webpage, I got it sorted. Thanks again!!!!

  • Daniele

    Great article, many thanks!

  • Dalibor

    Hi thank you for great article. Is it possible to combine more conditions? I tried to put and function inside if but it didnt work. Can you please help me?

  • HK

    seems that it cannot run in 2003

  • Dora R

    That formula is not working. I even tried the exact same information. I am using excel 2010. It gives me error: #value!

  • Dora R

    Thanks Debra!! I forgot that.

  • rendi

    Hi. I keep getting the #Num error when using this. Do you know why?

  • George

    Brilliant! Am at a coal mine in the middle of nowhere going through some data, and found your website in a frantic google search while sitting outside in the carpark with 1 bar of signal. Lifesaver. Cheers for the humourous instructions.

  • Borja

    Thank you very much, certainly useful and much simpler than I expected!

  • Sabyasachi Dey

    A 0 10 0
    B 12 0 0
    C 0 0 9

    How I will get the maximum nos by using if formula.

  • Joy

    THANK YOU SOOOOOOO MUCH!!!! I spent hours trolling around the internet trying to find a MAXIF-like formula. This website saved my PowerPoint deck (and my tail)

  • Claudio

    EXCELente !!! muchas gracias

  • Tim

    It's a shame such a normal (and useful) function as this still has to be done with the memory-hogging Array formula. I guess Microsoft won't get around to adding it as a normal function until Office 2025?

  • Jay

    This explanation was a major boon to my worksheet development. Thanks so much!

  • Thanks for this; is there anyway to know in advance whether I will need to convert formulas in excel into array formulas. Not 100% sure of when a formula should be an array vs a standard formula.

    ty in advance

  • Aaron

    That's great, but I'd like to take it a bit further. I'd like to combine the small/large functions with an if condition. Basically, I want to find the top/bottom 20 values in a column, given that 2 other conditions hold true. Does that make sense?

    I can obviously do this with a pivot table using filters, but prefer to avoid having to update them each time. Thanks.

  • Bondra Aji

    THANK YOU SO MUCH............!!!

  • Margi

    I have run into two issues...maybe someone can help.

    1) The calculations take a long time when adding this formula.
    2) if there is a #N/A in the data, it comes up with #N/A instead of ignoring it


    • Jack

      You probably found an answer to this as it was a few months ago, but if not, use:
      =IF(ISERROR(insert debra's fantastic formula here),"",insert debra's fantastic formula again)

      • Rob

        Why not just use:

        =IFERROR(insert Debra's fantastic formula here),"")

        This way you don't have to repeat the resource consuming Debra's fantastic formula...

    • Momin

      Very valid issue where this formula stumbles is that if the cells are blank or null, then it returns the min value as zero, and I solved it like this. It ignores all ZERO blank NA values and calculates perfectly:


  • Doug

    This is a great method and it seems to work very well. I have a situation where the data is not arranged in a "vertical" array. By this I mean the records are listed column-wise, not row-wise. Unlike the capability of the built-in function =averageif() which works both ways, I can't seem to get this method to work in an array where new records are added horizontally in columns.

    Can you verify if this is true?

    thanks, -doug

  • Marike

    Thnk you evry much. I've been working on this for quite a while, but this is an easy solution. Thanks for putting it online for everyone! I'm sure you've helped a lot of people, most people dont post a thankyou...

  • Jeff

    I love thinking out of the box! Great explination and very very useful!!

  • richard

    Thanks for wonderful explanation,,, its really informative!!!!

  • Jeff

    Thanks - this array function is working well for me as a substitute for MAXIFS in my local file. However, after I got is all tested and working, I posted these changes into the live file which is a shared Excel file on my company's internal network server. Turns out, it does not work well in a shared file. When one tries to copy, or insert rows, Excel 2007 returns this error:
    Error 1004:
    Cannot copy or move array entered formulas or data tables in a shared workbook.
    Does anyone know of any way to do MAXIFS without using array formulas? e.g. without using Ctrl+Shift+Enter?

  • Isuri

    Great article, thanks so much, saved me a lot of time.

  • Angela

    So I have used the MIN IF many times now, but for the first time I have gaps in the data, while the min function would traditional ignore blanks, the MIN IF inserts a 0. Certainly something to mindful of but does anyone know a work around?

  • John Hewitt


    This explanation is excellent and easily understood. More importantly, it has helped me immensely with several projects which would have been quite complicated without your great help.

  • This is great and works well. How can you change the formula to look up the Product column and then list each product and its corresponding highest or lowest value?

    Thus you don't have to referenc cell C11 i.e. here is a list of all my products show me the max/minmum for each.

  • Thomas Elliott

    Thank you!

  • [...] I think you are probably best to have a single worksheet that contains all the dumped data, along with the timestamp. Then have a second sheet that has a table for the different dates. Have a look at this ("Beyond the basics with min if") to show you how to use conditional formulas to extract only data that is relevant to that date. Finding MIN IF or MAX IF in Excel | Contextures Blog [...]

  • Rob

    Perfect! Just what I was looking for.

  • ADEL


  • agm


    If I enter "paper" in C12 and "pens" in C13, can I copy the array formula down? Don't seem to be able to do that - it still seems to link only to C11 (even if I remove the absolute reference)? Any help?

  • Michel

    Thank you for this post. That was exactly what I needed.
    Thks again for sharing.

  • Quynh

    Can I use this formula with 2 conditions?
    I try = MIN(IF(AND(..., ...),...)) --> Ctrl+Shift+ENter but it doesn't work :(
    Please advise.

  • hatim

    thank you that was easy and very helpful :-)

  • Rob

    Thanks, works great. I learnt something new today. The only issue is it's a bit processor heavy when dealing with large spreadsheets. Still I suppose there is no way round that. Thanks again.

  • Joe

    An easier-to-remember way to achieve this is using the SUBTOTAL formula. E.g. SUBTOTAL(5,filteredRange) will give the MIN value, and SUBTOTAL(4, filterRange) will give the MAX value.

  • Chris

    I've spent years dealing with donor ids and giving histories by gift and having to sort to only show the last gift. MAXIF would have saved me countless hours and headaches. Thanks so much!

  • Ngvancuong88

    Thank so much

  • […] a previous article, we combined the MAX and IF functions, to find the highest price for a specific […]

  • richard H

    It fails if you are using tables in 2013, eg: =MAX(IF(A2=Tx_Hist[@Description],Tx_Hist[@[Prd_date]])). It works OK for the first 2 rows, then returns zero for the rest of the table.

  • rayo

    wow thats a million this saved my time a hell lot!!!

  • James A

    Thanks for having such an awesome tutorial on MIN IF and MAX IF! It works perfectly in 2010!

  • Robert

    I want to extend this formula "one step" further. That is in column E let's say I have first names listed. So instead of finding the maximum value 20 for "paper", I want the formula to return the name in column E. Specifically cell E4 in the above example.

    I have found formulas that will return E4 if it is a number, but I get an error if the cell in non-numeric.

  • Momin

    This was very helpful, worked like a charm! saved me so much time, thanks you so much, God Bless you!

  • Andreas

    Very helpful. This formula is the best. Thank you so much :)

  • TINA

    Thank you so much for the Max(If) formula. It works perfectly when the data is in the same sheet. However, I have data in different sheets, and got back an error #REF!

    For example I have a sheet with 50 states listed down the page in column A, number of employee in column B and $Amount in column C. There are 25 sheets for different companies. I want to find the Max $Amount for each state among the 25 companies if number of employee is > 1000.

    Does the Max(If) works when your data is in different sheets? Thank you so much for your help on this.

  • Thiago

    Very helpful. Thank you so much!

  • MarkinID

    Thanks for the MaxIF suggestion. I needed to find a the maximum value less than 0 in an array with 2000+ entries. I wasn't looking forward to 2000+ IF statements then using MAX on these to find the answer. Several of the lookup type functions were checked and didn't work out.

    The key insight was to view this as array entered and either add the {} or use Ctrl+Shift+Enter.

    Your approach took 1 simple statement.



  • Jeanie Burdi

    Debra Dalgleish you rock thank you. You instructions were clear and precise. I was able to duplicate this the first time.

  • Apachenf

    Works a dream - many hours saved.

  • Jan

    Can we apply a formatting condition in this formula somehow?
    If I have a row of dates and the first four dates a highlighted green. How can I identify that last green date/cell?

    Thank you!

  • Sandeep

    Thanks a lot, information was really useful & I could able to use it for my work. I could able to write formula but missing {} brackets, but key trick mentioned was magic for me.

    Best Regards,

  • Octavian

    Many thanks, your article is still helping people after more than 3 years! ;-)

  • […] Creating a “MINIF” function similar to “AVERAGEIF”. What I like to do is create a column where I can put a 1 or 0 to toggle whether a comp is included or not. But I want to summarize that column using Min, Max, Median and Mean. But there is no such thing as a “MINIF” function similar to “AVERAGEIF”. Here is how you create one. […]

  • Tom

    Thank you for the post! I applied the Min IF function on a large dataset it returns values for about 35000 rows but fails beyond that. Does anyone have any good suggestion how to extend the rage of the function?

  • Musang Semput

    Thank you very much for the guidance
    It really helps!

  • Damian

    Thank you very much for this explanation. It worked perfectly. Can I ask a question just for lerning purposes: why doesnt it work with out presing ctrl shift enter?

    • Excel treats formulas as array formulas only when Ctl+Shift+Enter is pressed. In order to do a min of an array, we need to use array formulas. I was looking for a way to avoid array formulas, as they sometimes slow Excel down (if used in thousands of cells). So far, it looks like array formula is unavoidable. This method shown here is the only method I have been using. Thanks.

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>