Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to find the max or min value based on criteria in Excel?

doc-find-max-value-with-criteria-1

Supposing I have the following data range, column A contains the product names, and column B has the order quantities, now, I want to find the max order value of the product KTE as the following screenshot shown. In Excel, how could we extract the max or min value based on one or more criteria?

Find the Max or Min value based on only one criterion

Find the Max or Min value based on multiple criteria


Advanced Combine Rows: (Find the max or min value based on a key column)

With Kutools for Excel’s Advanced Combine Rows utiltiy, you can quickly combine multiple duplicate rows into one record based on key columns, and it also can apply some calculations such as sum, average, count and so on for other columns.

  • 1. Specify the key column that you want to find the max or min value that other column based on;
  • 2. Choose one calculation that you need.

doc-find-max-value-with-criteria-9

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Find the Max or Min value based on only one criterion

To return the max or min value with one criterion, the MAX function will do you a favor.

1. Enter this formula: =MAX((A2:A13=D2)*B2:B13) into a specific cell you want, see screenshot:

doc-find-max-value-with-criteria-2

Tips: In this formula: A2:A13 is the range cells which contain the criteria, D2 is the criterion you want to find the max value based on, B2:B13 is the range which return the corresponding value.

2. Then press Ctrl + Shift + Enter keys together to get the max value of KTE, see screenshot:

doc-find-max-value-with-criteria-3

Note:To get the min value based on the specific criterion, please enter this formula =MIN(IF(A2:A13=D2,B2:B13)), after typing the formula, you must press Ctrl + Shift + Enter keys together, then you will get the following result:

doc-find-max-value-with-criteria-4


arrow blue right bubble Find the Max or Min value based on multiple criteria

If you want to find the max or min value with multiple criteria, you can use the following formulas:

Take the following data for example, I need to find the max or min order of KTE in January:

doc-find-max-value-with-criteria-5

1. Enter this formula into a cell you want place the result : =MAX(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13))), see screenshot:

doc-find-max-value-with-criteria-7

Tips: In this formula: A2:A13 is the data which contains the criteria1, B2:B13 is the data range which contains the criteria2, F1 and F2 are the criteria you are based on, C2:C13 refers to the range you want to return the max value.

2. Then press Ctrl + Shift + Enter keys simultaneously, the max value where the corresponding value in A2:A13 equals the value in F1, and the corresponding value in B2:B13 equals the value in F2 is returned.

doc-find-max-value-with-criteria-8

Note: To get the min value based on these criteria, please apply this formula: =MIN(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13))), and remember to press Ctrl + Shift + Enter keys together.


Related articles:

How to select max data/value with max function in Excel?

How to select the highest value and lowest value in Excel?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Katya · 5 months ago
    Hello,

    Similar to above example, where the MAX array formula finds the max value for KTE and Jan (two conditions) --> i am trying to do almost same, but i need to return not just the maximum value, but the name of the row which contains this maximum value... Imagine, if there is one more column between B and C, which contains the name of the person who did the order, for example... I was trying to use INDEX formula, but it doesn't really work. Like this: =INDEX(C2:C13,MAX(IF(A2:A13=F1,IF(B2:B13=F2,D2:D13)),0) What can be the solution here?
  • To post as a guest, your comment is unpublished.
    narindran kumar · 1 years ago
    Hi,

    I would like to get help to retrieve a value from a table based on an Object's Name (Multiple objects with multiple data from a same objects which differentiate by date) to get the latest data based on specified date ( more latest data may be available than the specified data).

    No. Well Start Date End Date Oil Rates (stb/d)
    1 BT-101L 1/1/2017 0:00 1/2/2017 0:00 59
    2 BT-106L 1/7/2017 0:00 1/8/2017 0:00 124
    3 BT-106S 1/8/2017 0:00 1/9/2017 0:00 132
    4 BT-101L 1/9/2017 0:00 1/10/2017 0:00 138
    5 BT-201S 1/10/2017 0:00 1/11/2017 0:00 144
    6 BT-203S 1/11/2017 0:00 1/12/2017 0:00 150
    7 BT-101L 1/29/2017 0:00 1/30/2017 0:00 269

    In the results mode, I need to populate the data based on latest available data.

    Date BT-101L
    1-Jan 59
    2-Jan 59
    3-Jan 59
    4-Jan 59
    5-Jan 59
    6-Jan 59
    7-Jan 59
    8-Jan 132
    9-Jan 132
    10-Jan 132
    11-Jan 132
    12-Jan 132
    13-Jan 132
    14-Jan 132
    15-Jan 132
    16-Jan 132

    Can anyone help?
  • To post as a guest, your comment is unpublished.
    Garnster · 2 years ago
    Similar question but based on reversed information. I work in land investment. I have a list of counties and corresponding values. The lower the value the more I need to work in the area. If I plug say "County A" with a value of 100, then County B with a value of 85, then County C with a value of 66, what statement can I use to identify County C as the one I need to work on?
  • To post as a guest, your comment is unpublished.
    Virtualcoyright · 2 years ago
    Hi, when I try the MIN formula it doesn't work and just gives me 0??
    • To post as a guest, your comment is unpublished.
      Gadi Bizinyan · 6 months ago
      Virtualcoyright - Do you have blanks in the range you are using the MIN for? If so, it will return with the following formula 0:

      =MIN(IF(A2:A13=D2,B2:B13))

      To prevent it getting 0 if blanks exist in the range B2:B13, write the formula in the following manner:

      =MIN(IF(A2:A13=D2,IF(B2:B13<>0,B2:B13)))
      • To post as a guest, your comment is unpublished.
        Ahmed Ali · 4 months ago
        Sir you have just saved my bacon ! I was facing a problem that i didn't have any blanks & all values where > 0 but the minimum formula resulted in 0 while i wanted the least positive value and your modified formula did just that. I think they should modify the article to include your modified formula.