Find the lowest and highest values with the MIN and MAX functions. Use with the IF function, to lớn create MIN IF and MAX IF formulas, or use MINIFS and MAXIFS in Excel 365.
Bạn đang xem: Excel min and max function examples and video
MIN IF Formula
MAX IF Formula
MAX IF With Multiple Criteria
Get Latest Price for Specific Product
MIN IF & MAX IF with a Pivot Table
Get MIN / MAX Sample File
MIN and MAX Functions
To see a quick overview of how you can create your own MIN, MAX, MIN IF & MAX if formulas, you can watch this short video.
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 and MAX with Criteria
In Excel 2019, or Excel for Office 365, you can use the MINIFS và MAXIFS functions, shown below, khổng lồ find a minimum value, based on one or more criteria.
For earlier versions of Excel, use the MIN IF Formula or the MAXIFS Function shown below.
MINIFS Function
Use the MINIFS function lớn find the lowest number, based on one or more criteria.
MINIFS has 3 required arguments:
min_range: The range where the minimum value is found range1: The first range to kiểm tra for a criterion criteria1: The first criterion
MINIFS - One Criterion
For example, this formula (for Excel 365) finds the minimum quantity for the sản phẩm name in cell B6.
=MINIFS(tblProdCustThere"s a spill formula in cell B4 lớn create a unique danh mục of products. The MINIFS formula has a spill operator (#) at the over of that cell reference -- B4# -- so the MINIFS results spill down too.
Excel 2019: Spill formulas are not available in Excel 2019. In that version, refer to cell B4, without the spill operator, & copy the formula down manually.
=MINIFS(tblProdCust
MINIFS - Two Criteria
To use two or more criteria witn MINIFS, use the optional arguments for additional criteria ranges và criteria.
For example, this formula (for Excel 365) finds the minimum quantity for the customer selected in cell C3, and the product name in cell B6.
=MINIFS(tblProdCustThere"s a spill formula in cell B6 lớn create a unique list of products. The MINIFS formula refers lớn that cell with the spill operator -- B6# -- so the MINIFS results spill down too.
Excel 2019: Spill formulas are not available in Excel 2019. In that version, refer lớn cell B6, without the spill operator, và copy the formula down manually.
=MINIFS(tblProdCust
MAXIFS Function
In Excel 2019, or Excel for Office 365, you can use the MAXIFS function khổng lồ find a maximum value, based on one or more criteria. For earlier versions of Excel, use the MAXIFS Function shown below.
MAXIFS has 3 required arguments:
max_range: The range where the maximum value is found range1: The first range to kiểm tra for a criterion criteria1: The first criterion
MAXIFS - One Criterion
For example, this formula (for Excel 365) finds the maximum quantity for the hàng hóa name in cell B6.
=MAXIFS(tblProdCustThere"s a spill formula in cell B6 to lớn create a unique các mục of products. The MAXIFS formula has a spill operator (#) at the over of that cell reference -- B4# -- so the MAXIFS results spill down too.
Excel 2019: Spill formulas are not available in Excel 2019. In that version, refer to lớn cell B4, without the spill operator, & copy the formula down manually.
=MAXIFS(tblProdCust
MAXIFS - Two Criteria
To use two or more criteria witn MAXIFS, use the optional arguments for additional criteria ranges và criteria.
For example, this formula (for Excel 365) finds the maximum quantity for the customer selected in cell C3, & the hàng hóa name in cell B6.
=MAXIFS(tblProdCustThere"s a spill formula in cell B6 khổng lồ create a unique danh mục of products. The MAXIFS formula refers lớn that cell with the spill operator -- B6# -- so the MAXIFS results spill down too.
Excel 2019: Spill formulas are not available in Excel 2019. In that version, refer lớn cell B6, without the spill operator, and copy the formula down manually.
=MAXIFS(tblProdCust
MIN IF Formula
Although Excel has a SUMIF function và a COUNTIF function, there is no MINIF function. To lớn 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 danh sách with multiple products. The formula will be entered in cell D2, then copied down lớn D5.
First, enter the MIN and IF functions, và their opening brackets:
=MIN(IF(Next, select the sản phẩm names in the sales list, và press the F4 key, khổng lồ lock the reference.
=MIN(IF($G$2:$G$17Type an equal sign, & click on the cell with the hàng hóa name criteria. This reference will not be locked.
=MIN(IF($G$2:$G$17=C2Type a comma, then select the quantity cells in the sales list. Press the F4 key, to lớn lock this reference.
=MIN(IF($G$2:$G$17=C2,$H$2:$H$17To finish the formula, type two closing brackets, & 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 và 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. Khổng lồ fix it, click somewhere in the formula bar, và press Ctrl + Shift + Enter.
Then, copy the formula down, khổng lồ the rows below, to lớn 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. Lớn 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 danh sách with multiple products.
First, enter the MAX and IF functions, & their opening brackets:
=MAX(IF(Next, select the sản phẩm names in the sales list, và press the F4 key, khổng lồ lock the reference.
=MAX(IF($G$2:$G$17Type an equal sign, & click on the cell with the product name criteria. This reference will not be locked.
=MAX(IF($G$2:$G$17=C2Type a comma, then select the quantity cells in the sales list. Press the F4 key, to lớn lock this reference.
=MAX(IF($G$2:$G$17=C2,$H$2:$H$17To 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. Khổng lồ 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 hàng hóa names are listed.
Enter the following formula in cell D4, and array-enter it, by pressing Ctrl+Shift+Enter. Then, copy the formula down khổng lồ cell D7.
=MAX(IF($H$2:$H$17=C4,IF($I$2:$I$17=$D$1,$J$2:$J$17)))
Get Latest hàng hóa Price
If you have a danh mục of hàng hóa prices and dates, you can use a formula khổng lồ find the latest pricing date for a specific product:
Then, use SUMIFS or SUMPRODUCT to lớn get the price for that product, on that date.
Latest Price with MAXIFS Function
In Excel 2019, or Excel for Office 365, you can use this MAXIFS formula, to lớn find the latest price.
In this example, there is a price table with product, customer, date và price.
You could use two formulas in the solution - one to find the latest price, & one khổng lồ find the price for that date.
This formula in C4 returns the latest date for the selected product and customer:
=MAXIFS(tblPriceNext, this formula in cell D4 returns the price for that date, và the selected product & customer:
=SUMIFS(tblPriceOr, use this all-in-one formula, in cell D6, to find the price for the latest date:
=SUMIFS(tblPrice
Latest Price with MAX/IF Formula
In this example, there is a price danh mục with product, date and price.
To find the latest price for a specific product, start by using MAX & IF, khổng lồ get the latest date for that product. The sản phẩm name -- Pens -- is entered in cell A12.
To find the latest pricing date for that product, enter the following formula in cell B12, và press Ctrl + Shift + Enter:
=MAX(IF($A$2:$A$9=A12, $B$2:$B$9))
Next, lớn 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, & 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))
MIN IF & MAX IF with a Pivot Table
With some data, an easy way khổng lồ find the lowest và highest values for a specific item, is lớn use a pivot table. It automatically creates a menu of all the items, & you can show amounts as Min or Max.
This clip shows the steps, & there are written instructions below the video.
Pivot Table MIN IF and MAX IF
In the screen shot below, the TotalPrice field has been added twice to lớn the values area. In one column, the pivot field"s summary function has been changed to lớn MIN & in the other column it was changed to MAX.
From this pivot table, you can quickly see the minimum & maximum amounts based on a hàng hóa name. For example:
MIN IF Bran - 48.62 MAX IF Pretzels - 97.65Pivot Table MINIFS & MAXIFS
With a pivot table, you can also see minimum & maximum abounts based on multiple criteria -- like the Excel"s new MINIFS and MAXIFS functions. For example:
MINIFS East, Bars - 20MAXIFS West, Snacks - 114Pull MIN và MAX from Pivot Table
If you need to lớn use the Min và Max values from a pivot table in other formulas, use the GetPivotData Function. This video shows how to use the GetPivotData function, and there are written instructions below the video.
Xem thêm: Câu Hỏi Của Go Buster Red - Tổng Của Tử Số Và Mẫu Số Của Một Phân Số Bằng 25
To pull values from a pivot table, use the GetPivotData Function. In this example, you can select a region name in cell A4, và a category name in cell B4.
This formula is in cell C4, to lớn pull the minimum quantity for the selected region and category:
=GETPIVOTDATA("Min Qty",$A$7, "Region",A4, "Category",B4)This formula is in cell D4, to lớn pull the maximum quantity for the selected region and category: