To find the highest and lowest amounts in Excel, use the MAX and MIN function. If you need to use criteria in your calculations, add the IF function to your formula, in older versions of Excel. In Excel 2019 and Excel 365, use the newer MAXIFS and MINIFS functions. You can even get the answer without formulas – use a pivot table instead!
Simple MAX and MIN Formulas
If you just need to find the highest and lowest numbers in a list, a simple MIN or MAX formula will give you the answer.
For example, this formula finds the lowest number in the list of quantities, in cells F2:F7:
- =MIN(F2:F7)
There are more examples on the MIN and MAX page on my Contextures site.
MAX and MIN With Criteria
Sometimes, you need to find the highest or lowest amount for a specific item. To do that, you can add the IF function to the formula, in older versions of Excel.
For example, this formula finds the highest number for the product named in cell B1, with product names in E2:E7 and quantities in cells F2:F7:
- =MAX(IF($E$2:$E$7=B1,$F$2:$F$7))
NOTE: In Excel 2016 and earlier, this formula must be array entered, by pressing Ctrl+Shift+Enter
Video: MIN and MAX with Criteria
To see a quick overview of how you can create MIN, MAX, MIN IF and MAX if formulas, you can watch this short video.
MINIFS and MAXIFS
In Excel 2019, or Excel for Office 365, you can use the MINIFS and MAXIFS functions, shown below, to find a minimum value, based on one or more criteria.
The MINIFS and MAXIFS functions have 3 required arguments:
- range with values
- criteria1 range
- criteria1 value
You can add more criteria ranges, and their values, if needed.
In this example, the formula finds the lowest quantity based on a product name in cell A1, and a customer code in cell B1:
- =MINIFS($F$2:$F$7,$D$2:$D$7,$A$1,$E$2:$E$7,$B$1)
MIN and MAX with a Pivot Table
Another way to find MIN and MAX with criteria is to use a pivot table.
- Add the quantity field to the Values area twice
- Then, use the Summarize Values By feature to set one column as the Min, and the other column as Max.
This video shows the steps, and there is more information on the MIN and MAX page on my Contextures site.
Get the Sample Files
For more MIN and MAX examples, and to get the sample Excel files, go to the MIN Function and MAX Function page on my Contextures site.
There are 3 zipped workbooks, and all are in xlsx format, with no macros.
________________
Find MAX and MIN With Criteria in Excel Formulas
________________