Excel MIN and MAX Functions

Search Contextures Sites

Excel MIN and MAX Functions

MIN Function
MAX Function
MIN IF Formula
MAX IF Formula
Watch the MIN and MAX Function Video
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)

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.

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.

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

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

Privacy Policy

Contextures Inc., Copyright Š2012
All rights reserved.

Debra Dalgleish

Last updated: July 30, 2012 2:34 PM

	Excel MIN and MAX Functions MIN Function MAX Function MIN IF Formula MAX IF Formula Watch the MIN and MAX Function Video 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) 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. 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.