Find the lowest and highest values with the MIN and MAX functions. Use with the IF function, to create MIN IF and MAX IF formulas.
MIN Function
To find the lowest value in a range of cells, use the MIN function. For example, this formula will find the lowest value in cells H2:H17
=MIN(H2:H17)
MAX Function
To find the highest value in a range of cells, use the MAX function. For example, this formula will find the highest value in cells H2:H17
=MAX(H2:H17)
MIN IF Formula
Although Excel has a SUMIF function and a COUNTIF function, there is no MINIF function. To create your own MINIF, you can combine the MIN and IF functions in an array formula.
In this example, we'll find the lowest value for a specific product in a sales list with multiple products. The formula will be entered in cell D2, then copied down to D5.
First, enter the MIN and IF functions, and their opening brackets:
=MIN(IF(
Next, select the product names in the sales list, and press the F4 key, to lock the reference.
=MIN(IF($G$2:$G$17
Type an equal sign, and click on the cell with the product name criteria. This reference will not be locked.
=MIN(IF($G$2:$G$17=C2
Type a comma, then select the quantity cells in the sales list. Press the F4 key, to lock this reference.
=MIN(IF($G$2:$G$17=C2,$H$2:$H$17
To finish the formula, type two closing brackets, and then press Ctrl+Shift+Enter to array-enter the formula.
=MIN(IF($G$2:$G$17=C2,$H$2:$H$17))
In the formula in the Formula Bar, shown above, you can see that curly brackets were 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.
Then, copy the formula down, to the rows below, to see the minumum for each of the products.
MAX IF Formula
Although Excel has a SUMIF function and a COUNTIF function, there is no MAXIF function. To create your own MAXIF, you can combine the MAX and IF functions in an array formula.
In this example, we'll find the highest value for a specific product in a sales list with multiple products.
First, enter the MAX and IF functions, and their opening brackets:
=MAX(IF(
Next, select the product names in the sales list, and press the F4 key, to lock the reference.
=MAX(IF($G$2:$G$17
Type an equal sign, and click on the cell with the product name criteria. This reference will not be locked.
=MAX(IF($G$2:$G$17=C2
Type a comma, then select the quantity cells in the sales list. Press the F4 key, to lock this reference.
=MAX(IF($G$2:$G$17=C2,$H$2:$H$17
To finish the formula, type two closing brackets, and then press Ctrl+Shift+Enter to array-enter the formula.
=MAX(IF($G$2:$G$17=C2,$H$2:$H$17))
In the formula in the Formula Bar, shown above, you can see that curly brackets were 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.
Then, copy the formula down, to the rows below, to see the minumum for each of the products.
MAX IF With Multiple Criteria
In the previous example, we found the highest quantity for a specific product, so there was just one criterion -- the product name.
You can also use the MAX IF technique with multiple criteria, by including additional IF functions in the formula. For example, if the data includes a customer name, we could find the highest quantity for each product, for a specific customer.
The customer name is entered in cell D1. In cells C4:C7, the product names are listed.
Enter the following formula in cell D4, and array-enter it, by pressing Ctrl+Shift+Enter. Then, copy the formula down to cell D7.
=MAX(IF($H$2:$H$17=C4,IF($I$2:$I$17=$D$1,$J$2:$J$17)))
- The formula checks column H for product names that match the entry in cell C4.
- Then, it checks column I for customer names that match the name in cell D1.
- For those rows, it finds the highest amount in column J.
Get Latest Price for Specific Product
If you have a list of product prices and dates, you can use the MAX/IF technique to find the latest pricing date for a specific product. Then, use SUMIFS or SUMPRODUCT to get the price for that product, on that date.
In this example, there is a price list in cells A1:C9.
To find the latest price for a specific product, start by using MAX and IF, to get the latest date for that product. The product name -- Pens -- is entered in cell A12.
To find the latest pricing date for that product, enter the following formula in cell B12, and press Ctrl + Shift + Enter:
=MAX(IF($A$2:$A$9=A12,$B$2:$B$9))
Next, to find the price for that product, on that date, enter the following SUMIFS formula in cell C12:
=SUMIFS($C$2:$C$9,$A$2:$A$9,A12,$B$2:$B$9,B12)
The SUMIFS function is available in Excel 2007, and later versions. For earlier versions of Excel, you can use the SUMPRODUCT function:
=SUMPRODUCT(($A$2:$A$9=A12)*($B$2:$B$9=B12)*($C$2:$C$9))
Watch the MIN and MAX Function Video
To see a quick overview of how you can create your own MIN, MAX, MIN IF and MAX if formulas, you can watch this short video.
Download the MIN and MAX Sample File
To see the MIN and MAX formulas, you can download the MIN and MAX sample file. The file is in Excel 2007/2010 formatt, and zipped.
For more information on array formulas, I recommend Mike Girvin's book, Ctrl+Shift+Enter: Mastering Excel Array Formulas.