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?



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
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 · 1 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 · 1 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 · 1 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 · 2 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 · 6 days 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.