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

or
0
0
0
s2smodern

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


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

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    narindran kumar · 7 months 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 · 10 months 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 · 11 months ago
    Hi, when I try the MIN formula it doesn't work and just gives me 0??