Here as we can see in the above screenshot MAX IF function check for the category name in the D column as well as in the F column, and if it matches, it will return the largest value here in this example, we got the output as 330 which is a largest sales value in the category. Now the if condition will check for the entire category name in C2 to C13 column whether it matches with the mentioned Category name in the F4 column if the category name matches, it will return the largest sales value from the selected D column as D2 to D13 and lock the cells by pressing F4 where we will get the $ symbol to indicate that cells are locked, and we have given the Formula as = MAX(IF($C$2:$C$13=F4,$D$2:$D$13)).Īfter the MAXIF function is completed by closing all the brackets now, hold SHIFT+CTRL and then press enter as SHIFT+CTRL+ENTER so that we will get the output in array formula where we will get open and closing parenthesis at the opening and end of the statement, which is shown in the below screenshot. In the above formula, we have applied MAX and IF function and then we selected Column C2 to C13 where we have listed the category name as C2:C13 and then we have checked the condition by selecting the cell as F4 where the F4 column is nothing but Category Name. Step 1 – First, we will create a new table with a category name to display the result, as shown below. Now we will apply the Excel MAX IF function to find out the maximum sales value by following the below steps. In such a case, we can combine MAX and If Function with an array formula so that it makes the task very easier to find out the largest sales value category wise in Excel.Ĭonsider the below example, which has Product Name, Category and Sales value which is shown below. Using only the max function, we can check only the maximum value, but we cannot check the maximum value category wise if the sales data contain a huge category and sales value. Assume that we need to check the largest sales value category wise. In this example, we will learn how to use MAX IF function in Excel. In the below result, the Max function will check for the largest number in the D column and return the maximum sales value as 1250.Įxample #2 – Using MAX IF Function in Excel with an array formula ![]() Step 3 – Press enter so that we will get the MAX value is 1250, which is shown in the below screenshot. Step 1 – First, select the new cell, i.e. Now we will use the MAX function to find out which has the largest sales value by following the below steps. In this example, we will first learn how to use the MAX function with the below example.Ĭonsider the below example, which shows sales data of the product category wise. You can download this MAX IF Function Excel Template here – MAX IF Function Excel Template Example #1 – MAX Function in Excel ![]()
0 Comments
Leave a Reply. |