Formulas > > MIN and MAX Excel MIN and MAX FunctionsFind lowest and highest numbers with Excel MIN and MAX functions. For minimum and maximum with criteria, use with IF function, to create MIN IF and MAX IF formulas, or use MINIFS and MAXIFS in Excel 365. |
MIN and MAX with CriteriaIn Excel 2019, or Excel for Office 365, you can use the MINIFS and MAXIFS functions, shown below, to find a minimum value, or maximum value, based on one or more criteria. For earlier versions of Excel, the MINIFS and MAXIFS functions are not available, so you can use the MIN IF Formula or the MAXIFS Function shown below. MINIFS FunctionUse the MINIFS function to find the lowest number, based on one or more criteria. MINIFS has 3 required arguments in its syntax:
MINIFS - One CriterionFor example, this formula (for Excel 365) finds the minimum quantity for the product name in cell B6.
There's a spill formula in cell B4 to create a unique list of products. The MINIFS formula has a spill operator (#) at the end 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, and copy the formula down manually.
MINIFS - Two CriteriaTo use two or more criteria with MINIFS, use the optional arguments for additional criteria ranges and criteria. MINIFS has optional pairs of arguments in its syntax, for criteria ranges and 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.
There's a spill formula in cell B6 to create a unique list of products. The MINIFS formula refers to 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 to cell B6, without the spill operator, and copy the formula down manually.
|
MAXIFS FunctionIn Excel 2019, or Excel for Office 365, you can use the MAXIFS function to find a maximum value, based on one or more criteria. For earlier versions of Excel, use the MAX IF With Multiple Criteria formula shown below. MAXIFS has 3 required arguments in its syntax:
MAXIFS - One CriterionMAXIFS has optional pairs of arguments in its syntax, for criteria ranges and criteria:
For example, this formula (for Excel 365) finds the maximum quantity for the product name in cell B6.
There's a spill formula in cell B6 to create a unique list of products. The MAXIFS formula has a spill operator (#) at the end 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 cell B4, without the spill operator, and copy the formula down manually.
MAXIFS - Two CriteriaTo use two or more criteria with MAXIFS, use the optional arguments for additional criteria ranges and criteria. For example, this formula (for Excel 365) finds the maximum quantity for the customer selected in cell C3, and the product name in cell B6.
There's a spill formula in cell B6 to create a unique list of products. The MAXIFS formula refers to 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 to cell B6, without the spill operator, and copy the formula down manually.
|
Get Latest Product PriceIf you have a list of product prices and dates, you can use a formula 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. Latest Price with MAXIFS FunctionIn Excel 2019, or Excel for Office 365, you can use this MAXIFS formula, to find the latest price. In this example, there is a price table with product, customer, date and price. You could use two formulas in the solution - one to find the latest price, and one to find the price for that date. This formula in C4 returns the latest date for the selected product and customer:
Next, this formula in cell D4 returns the price for that date, and the selected product and customer:
Or, use this all-in-one formula, in cell D6, to find the price for the latest date:
Latest Price with MAX/IF FormulaIn this example, there is a price list with product, date and price. 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:
Next, to find the price for that product, on that date, enter the following SUMIFS formula in cell C12:
The SUMIFS function is available in Excel 2007, and later versions. For earlier versions of Excel, you can use the SUMPRODUCT function:
|
MIN IF and MAX IF with a Pivot TableWith some data, an easy way to find the lowest and highest values for a specific item, is to use a pivot table. It automatically creates a list of all the items, and you can show amounts as Min or Max. This video shows the steps, and there are written instructions below the video. Pivot Table MIN IF and MAX IFIn the screen shot below, the TotalPrice field has been added twice to the values area.
From this pivot table, you can quickly see the minimum and maximum amounts based on a product name. For example:
Pivot Table MINIFS and MAXIFSWith a pivot table, you can also see minimum and maximum amounts based on multiple criteria -- like the Excel's new MINIFS and MAXIFS functions. For example:
Pull MIN and MAX from Pivot TableIf you need to use the Min and 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. Get MINIFS and MAXIFS with GETPIVOTDATATo pull values from a pivot table, use the GetPivotData Function. In this example, you can select a region name in cell A4, and a category name in cell B4. This formula is in cell C4, to pull the minimum quantity for the selected region and category:
This formula is in cell D4, to pull the maximum quantity for the selected region and category:
|
Get the Sample Files
More Tutorials |
Last updated: August 19, 2023 3:41 PM