How to find the max or min value based on criteria in Excel?
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.
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:
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:
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:
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:
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:
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.
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.
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
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 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...