MS Excel: Max If without If

When will start looking for advice how to make Max If formula in MS Excel, most of the time you will get this example:

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

Where those curly brackets mean that this formula is an array formula. And while it is only one IF criteria, formula look fine. But lets add second criteria and we will get this formula:

{=MAX(IF(criteria_range_1=criteria_1, IF(criteria_range_2=criteria_2, value_range)))}

With two criteria formula became more confusing as personally for me, more IFs we came in our formula, more confusing formula gets. So I not even try to write formula with three criteria.

So lets get rid of those IFs and write formula with one criteria.

{=MAX((criteria_range=criteria)*(value_range))}

At least for me this formula looks much easier to understand. Now lets jump directly to formula with 3 criteria.

{=MAX((criteria_range_1=criteria_1)*(criteria_range_2=criteria_2)*(criteria_range_2=criteria_2)*(value_range))}

Just don’t forget that this formula is also an array formula and to make it that you have to press Ctrl + Alt + Enter.

So here we have three criteria and this formula still easy to read. So I think that it is a great way to get Max If with multiple criteria and to not get lost.

So be creative and stay smart and you will find more ways how to write more readable formulas.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

five × three =