Conditional maximum/minimum with multiple criteria in Excel

Hi reader,

Find out how to do a maximum/minimum if. Consider the example bellow:

• Define the criterias you want to apply to get the extrema.
• In this example I want to get maximum "Total Price" where segment is "Mobile Phone" and volume is higher than 10 units
• To get the conditional maximum, insert the following formula on the selected cell (e.g. cell E13): 

• "=MAX(IF((range_1=condition_1)*(range_2=condition_2);range_max)" where:
• range_1: range to apply condition_1 (e.g. column B2:B11)
• condition_1: condition to apply on range_1 (e.g. "Mobile Phone")
• range_2: range to apply condition_2 (e.g. column C2:D11)
• condition_2: condition to apply on range_2 (e.g. 10)
• range_max: range to get the maximum value in accordance with conditions defined previously(e.g. column E2:D11)
• If you want to apply a different condition than equal to, just change "=" to ">" or "<" (if you want to apply condition higher than or lower than, respectively)
• Press crtl + shift + enter

In the example, the formula would be:

• To get conditional minimum, just change MAX on the formula for a MIN.

Hope it makes your spreadsheets easier.