Find the maximum numerical value in a list based on a criteria in Excel

To find the maximum numerical value in a list based on a criteria, we can use the MAX function along with the If function in Excel.

First we will learn how to use Max function in Microsoft Excel.

MAX: This function is used to return the largest number in a set of values. It ignores logical values and text.
 

 
Example: Range A1:A4containsnumbers, we need to return the maximum number from this range.

Follow the below given steps:-

• Select the cell B1 and write the formula.
• =MAX(A1:A4), press Enter on your keyboard.
• The function will return 9.
• 9 is the maximum value in the range A1:A4.

 

 
Let’s take an example to understand how we can find the maximum numerical value in a list based on the criteria.

We have data for certain items in the range A2:D24. Column A contains items, Column B contains zone and column C contains sold unit.  We need to find the Maximum Units sold for each item zone-wise.
 

 
Follow the below given steps:-

• Select the cell D2.
• Write the formula to find out the maximum value
• =MAX(($A$2:$A$31=A2)*($B$2:$B$31=B2)*($C$2:$C$31))
• Press Ctrl+Shift+Enter

• The function will return the maximum value in the east zone for handsets.

• By using this formula, you can find out the maximum value for each item zone-wise.

 

 
Note:- This is an array function, so you cannot paste the formula in multiple cells. You have to paste the formula one by one in each cell.

This is the way we can find out the maximum number in the list based on criteria in Microsoft Excel.
 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com