Search Contextures Sites

Excel MIN and MAX Functions

MIN Function
MAX Function
MIN IF Formula
MAX IF Formula
MAX IF With Multiple Criteria
Get Latest Price for Specific Product
Video: MIN and MAX Functions
Download the MIN and MAX Sample File
Excel Function Tutorials

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)

MIN function

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)

MAX function

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.

MIN IF formula

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

MIN IF formula

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.

MIN IF formula copy

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.

MAX IF formula

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

MAX IF formula

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 copy

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

max if customer qty

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

price list with dates

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

MIN and IF find latest date

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)

SUMIFS finds latest price

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

SUMIFS finds latest price

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.

 

30 Excel Functions in 30 Days

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

Learn how to create Excel dashboards.

 

 

30 Excel Functions in 30 Days

 

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

Learn how to create Excel dashboards.

 

Learn how to create Excel dashboards.

 

 

Excel Function Tutorials

Excel SUM Function  
Excel VLOOKUP Function  
Excel INDEX function and Excel MATCH Function   
Excel Count Function  
Excel INDIRECT Function  

Excel Video Tutorials

Excel MATCH Function Video Tutorial  
Excel VLOOKUP Function Video Tutorial 
Hide Excel VLOOKUP Function Errors With IF and ISNA 

   

 

Privacy Policy

 

Contextures Inc., Copyright ©2014
All rights reserved.

 

Last updated: December 23, 2013 5:55 PM