However, we need to replace all "x" values in column C with zeros in this case because SUMPRODUCT MAX only works with numeric data: The max value with the same conditions can also be found by using this non-array formula: =MAX(IF((B2:B10=F1) + (B2:B10=H1), C2:C10))Įnter the formula by pressing the Ctrl + Shift + Enter key combination and you will get this result: With the rounds listed in B2:B10, the results in C2:C10 and criteria in F1 and H1, the formula goes as follows:
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.
0 kommentar(er)
