Then we use the rank formula for identifying the smallest (or largest in case) and return the corresponding value with a simple VLOOKUP. The basic idea is to only show the values which the conditions apply for. If you don’t want to go with VBA or array formulas, there are workarounds for getting the minimum or maximum value under one or more conditions.
Workaround for getting the minimum value under a condition We want to know the lowest price of a blue VW Golf: =MINIFS(C:C,A:A,"VW Golf",B:B,"Blue")įor more information on the MINIFS formula, please refer to this article provided by Microsoft. Let’s take a look at an easy example: We got car names in column A, their colors in column B and their prices in column B. If you got more criteria, you can just extend the formula and repeat number 2 and 3 from above. The first criteria: This is the value, which Excel searches for in the first criteria range.The first criteria range: The range of cells which contain the first criteria.The minimum (or maximum) range: In this range, Excel will search for the minimum or maximum value and return it.MINIFS and MAXIFS work pretty much the same way as SUMIFS. Furthermore, you have to make sure that everybody who works on your file should have Office 2016 as they otherwise will get #NAME? errors. So the bad news is: If you don’t have the latest version of Excel, you can’t use these formulas. Since Office 2016, Microsoft finally introduced built-in MINIFS and MAXIFS formulas. MINIFS and MAXIFS formula since Excel 2016