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
________________
Hi Debra,
I really enjoy your tutorials, they are well presented and help solve daily challenges in analysis. I appreciate that you provided this min/max recommendation with arrays since it will be helpful to create a summary with a staffing plan. As you can imagine in a large company there will be disparate pay levels within job positions; your example today will help quickly illustrate the range of pay between specific positions. Our spreadsheet contains over 1,000 rows and this will help – I sometimes prefer formulas since they are always true, versus a pivot table that needs to be “refreshed”. I’m wondering if you can extend the if statement by nesting a second if formula to define a min/min within a determined range (not the full range of a category). In much older prior versions of Excel, I ran into an issue where Excel limited the number of If statements within one formula. I don’t know if you ever had the same issue.
Thanks for your awesome blog and video tutorials.
-Krist
Thanks, Krist, and you can use the multiply operator to include more criteria. For example:
=MAX(IF(($D$2:$D$7=$A$1)*($E$2:$E$7=$B$1),$F$2:$F$7))
array-entered with Ctrl+Shift+Enter