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:

=MIN(D2:D8)

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

=MAX(D2:D8)

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:

=MIN(IF(

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

=MIN(IF(C2:C8=C11

MinIfMaxIf04

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

=MIN(IF(C2:C8=C11,D2:D8

MinIfMaxIf05

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

=MIN(IF(C2:C8=C11,D2:D8))

MinIfMaxIf06

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.

MinIfMaxIf07

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

=MIN(IF($C$2:$C$8=$C$11,$D$2:$D$8))

Create a MAXIF Formula

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

=MAX(IF($C$2:$C$8=$C$11,$D$2:$D$8))

And remember to press Ctrl+Shift+Enter

MinIfMaxIf08

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.

 

________________

59 comments to Finding MIN IF or MAX IF in Excel

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=""> <strike> <strong>