Skip to main content

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

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.

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!

Related articles:

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

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

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Spreadsheets: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 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!
Comments (10)
No ratings yet. Be the first to rate!
EXCEL GOD
EXCEL GOD
This comment was minimized by the moderator on the site
Tip: use MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) or MINIFS...


The solution presented here is stupid and doesn't really work.
Reply
0
0
MJ
MJ
This comment was minimized by the moderator on the site
This was super helpful, thank you so much!
Reply
0
0
Hazi
Hazi
This comment was minimized by the moderator on the site
i'm finding min value from each cell (which not in a range), and it give me answer '-'. how to solve this? =min(A3, B3, D3, G3). but the B3 is zero.
Reply
0
0
Katya
Katya
This comment was minimized by the moderator on the site
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?
Reply
0
0
narindrankumar@gmail.com
This comment was minimized by the moderator on the site
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?
Reply
Report
0
0
Garnster
Garnster
This comment was minimized by the moderator on the site
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?
Reply
0
0
Virtualcoyright
Virtualcoyright
This comment was minimized by the moderator on the site
Hi, when I try the MIN formula it doesn't work and just gives me 0??
Reply
0
0
Gadi Bizinyan
Gadi Bizinyan     Virtualcoyright
This comment was minimized by the moderator on the site
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)))
Reply
0
0
aamostafa87@gmail.com
    Gadi Bizinyan
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations