< Go Back

Max if criteria match

If you want to get the highest value from a range of values using a particular criterion, then the combination of MAX and IF function can be used to filter the array of values, while returning the max value.

Formula

=MAX(IF(criteria_range=criteria,value_range))

How to use Max if criteria match

In order to get the best out of the formula, it is necessary for the data to be purposefully listed, and the arguments for the formula to be properly understood:

Bearing that in mind, let us understand the formula argument –

  • Criteria_range: This represents the array list. It is a range of cell values and hence will look like this B2:B500. This range of heterogeneous values contains other subsets of homogeneous values. The criteria range is the filter boundary.
  • Criteria: This represents the actual value that forms the basis for the filtering process.
  • Value_range: This represents the array list of values upon which the filtering is done to determine the maximum.

Example

Let us assume for a moment that data is collected after studying the distance covered by a man on the first 10 days of January and February. To find the maximum distance covered in January, we do the following:

  1.     Set the values

Figure 1 – Data table

  1.     Prepare the criteria and result table

Figure 2 – Showing result table for criteria value and longest distance

  1.     Enter the criteria

Figure 3 – Criteria entered as January

  1.     Input the IF function formula

Figure 4 – Showing the formula to get the longest distance walked

  1.     View Result

Figure 5 – Showing result of ‘max if criteria match’

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar