How To Find A Maximum Value In Excel

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 production names, and column B has the guild quantities, now, I want to find the max order value of the product KTE every bit the post-obit screenshot shown. In Excel, how could we extract the max or min value based on one or more than criteria?

Notice the Max or Min value based on but one criterion

Find the Max or Min value based on multiple criteria

Find the Max or Min value based on merely i criterion

To return the max or min value with 1 criterion, the MAX function volition practise yous a favor.

ane. Enter this formula: =MAX((A2:A13=D2)*B2:B13) into a specific cell yous want, run across screenshot:

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

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

2. Then printing Ctrl + Shift + Enter keys together to go the max value of KTE, meet screenshot:

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

Note：To get the min value based on the specific criterion, delight enter this formula =MIN(IF(A2:A13=D2,B2:B13)) , after typing the formula, you must printing Ctrl + Shift + Enter keys together, so yous volition get the following outcome:

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

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 employ the post-obit formulas:

Take the following information for instance, I need to find the max or min club of KTE in January:

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

1. Enter this formula into a cell you want identify the result : =MAX(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13))) , run across 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 lot are based on, C2:C13 refers to the range yous desire to return the max value.

2. So 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 call back to printing Ctrl + Shift + Enter keys together.

Advanced Combine Rows: (Discover the max or min value based on a central column)

With Kutools for Excel's Advanced Combine Rows utiltiy, yous tin quickly combine multiple duplicate rows into one record based on primal columns, and information technology too can apply some calculations such equally 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;
two. Choose one adding that you lot need.

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

Kutools for Excel: with more than 200 handy Excel add together-ins, free to effort with no limitation in 60 days. Download and gratuitous 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?

The Best Part Productivity Tools

Kutools for Excel Solves Well-nigh of Your Problems, and Increases Your Productivity past 80%

Reuse: Chop-chop insert circuitous formulas, charts and annihilation that you lot have used earlier; Encrypt Cells with password; Create Mailing Listing and transport 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 Information; Separate Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Detect and Fuzzy Discover in Many Workbooks; Random Select...
Verbal Copy Multiple Cells without irresolute formula reference; Motorcar Create References to Multiple Sheets; Insert Bullets, Bank 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 (relieve and utilize filter schemes to other sheets); Avant-garde Sort by month/week/mean solar day, frequency and more than; Special Filter by assuming, 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. Like shooting fish in a barrel deploying in your enterprise or organization. Full features 30-twenty-four hour period complimentary trial. 60-twenty-four hours money dorsum guarantee.

kte tab 201905

Part Tab Brings Tabbed interface to Office, and Brand 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 past 50%, and reduces hundreds of mouse clicks for you every twenty-four hours!

officetab bottom