Find MAX and MIN With Criteria in Excel Formulas

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.

MIN and MAX formulas
MIN and MAX formulas

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

MIN IF and MAX IF formulas
MIN IF and MAX IF formulas

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)
MINIFS function with 2 criteria
MINIFS function with 2 criteria

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

Find MAX and MIN With Criteria in Excel Formulas
Find MAX and MIN With Criteria in Excel Formulas

________________

Original source: https://contexturesblog.com/archives/2021/07/22/find-max-and-min-with-criteria-in-excel-formulas/?utm_source=rss&utm_medium=rss&utm_campaign=find-max-and-min-with-criteria-in-excel-formulas

Leave a Reply

Close Menu