Please pay attention that in the Excel language, the task is formulated differently: return the max value if round is either 2 or 3. =SUMPRODUCT(MAX((( criteria_range1= criteria1) + ( criteria_range2= criteria2)) * max_range))Īs an example, let's work out the best result in rounds 2 and 3. goes to the SUMPRODUCT function and it outputs the max number in a cell. In Excel 2013 and earlier versions, you still have to create your own array formula by combining the MAX function with an IF statement:
A while ago, they introduced MAXIFS, and now the users of Excel 2019 and Excel 2016 included with Office 365 subscriptions can do conditional max an easy way. Until recently, Microsoft Excel did not have a built-in MAX IF function to get the maximum value based on conditions. This can be done by using a few different formulas, and this article explains all possible ways. In some situations, however, you may need to drill down into your data further to find the max value based on certain criteria. In our previous tutorial, we looked at the common uses of the MAX function which is designed to return the largest number in a dataset. The article shows a few different ways to get the max value in Excel based on one or several conditions that you specify.
