Searching for some easy alternatives to evaluate maximum value from the data under the conditions in Excel?
Stop searching for it, as by using the Excel MAXIFS function you can easily perform this task.
Now the question arises, how to use MAXIFS in Excel …?
Even if you don’t know about this, don’t get tensed…! As in this tutorial you will get all the required information regarding MAXIFS function.
So, quickly scroll down to this post to grab complete detail on MAXIFS function and how to use it.
What Is Maxifs Formula?
Excel MAXIFS functions basically return biggest numeric value that matches one or more criteria in the range of values.
MAXIFS is used with the criteria based on the text, numbers, dates and other conditions.
Objective to use:
To get the maximum value using the criteria
=MAXIFS (max_range, range1, criteria1, [range2], [criteria2], …)
- max_range– Range of values is used for determining the maximum result.
- range1– 1st range to evaluate.
- criteria1– criteria for using the range1.
- Range2– [optional] 2nd range to evaluate.
- Criteria2– [optional] criteria for using the range2.
Excel 2019, Excel 365
How To Use MAXIFS In Excel?
Maxif function returns largest numeric value which matches with one or more assigned criteria.
To apply criteria over dates, text and numbers you can apply MAXIFS functions.
This function supports wildcards (*,?) and logical operators (>,<,<>,=) for the partial matching.
It mainly takes 3 required arguments:
max_range, range1, and criteria1.
Using these 3 arguments this MAXIFS function gives the largest number in the max_range where range1 corresponding cells matches with the condition assigned by the criteria1.
If you want to add some more conditions then apply it using the criteria/range pairs.
2nd condition is defined by criteria2 and range2 and so on. Maximum this function allows 126 pairs of range/criteria.
MAXIFS is basically a group of 8 functions which divides the logical criteria into two main parts i.e criteria + range.
Due to this, syntax which is used for making criteria is quite different. MAXIFS needs cell range for the range arguments. So you are not allowed to use array.
MAXIFS is a newly added function in Excel application, thus it is available only in Excel 2019 and 365 version. Whereas, in the previous Excel version you have to use array formula which is mainly based on the IF and MAX to extract maximum values using the criteria.
Examples To Show How To Use Maxifs In Excel
Let’s know how to use maxifs in Excel more clearly with some examples. I have explained how to use Excel maxifs function in one criterion and two criterions.
MAXIFS – One Criterion
Following formula will fetch the maximum quantity of the product name which is present in cell B6.
=MAXIFS(tblProdCust[Qty], tblProdCust[Product], B4#)
To make product’s unique list spill formula is applied on the cell B6. MAXIFS formula is included with spill operator (#) which is at the end of that particular cell reference B4#. This will spill down the MAXIFS result.
You won’t get this Spill formula in Excel 2019 version. So in this Excel version, you need to refer this cell B4, but without spill operator after that copy the manually.
=MAXIFS(tblProdCust[Qty], tblProdCust[Product], B4)
MAXIFS – Two criterion
In the shown example, the formulas in G5 and G6 are:
=MAXIFS(D5:D16,C5:C16,”F”) // returns 93
=MAXIFS(D5:D16,C5:C16,”M”) // returns 83
In 1st formula, MAXIFS gives the maximum value within D5:D16 whereas C5:C16 is equal to “F” (93).
In 2nd formula, MAXIFS returns the maximum value in D5:D16 whereas C5:C16 is equal to “M” (83).
In the given example, MAXIFS function is used with 2 criteria. One for the group and another is for the Gender.
Conditions are included in the range/criteria pairs whereas E5:E16 is paired along with the “B” condition.
Formulas in H5:I6 are:
H5=MAXIFS(D5:D16,C5:C16,”F”,E5:E16,”A”) // returns 93
I5=MAXIFS(D5:D16,C5:C16,”F”,E5:E16,”B”) // returns 85
H6=MAXIFS(D5:D16,C5:C16,”M”,E5:E16,”A”) // returns 83
I6=MAXIFS(D5:D16,C5:C16,”M”,E5:E16,”B”) // returns 79
To get the maximum value in the A1:A100 when the cells in B1:B100 are more than 50.
In order to maximum value in A1:A100 when cells in B1:B100 are equal to or less than 100. Cells in C1:C100 are more than 0.
Not equal to
To apply the criteria of “not equal to” you need to use “<>” operator enclosed within the double quotes (“”).
E.g, to get the maximum value in A1:A100 when the cells in B1:B100 is not equal to “red”.
Value From Another Cell
If you are using value of another cell in your condition then the cell reference must need to be concatenated with the operator.
e.g: in order to get the maximum value within A1:A100 when the cells present in B1:B100 are more than the value of C1.
You need to only keep the sign of greater operator (>) enclosed within the quotes (“”).
In the MAXIFS criteria you can make use the wildcard characters like, asterisk (*), question marks (?) or tilde (~).
A question mark (?) is used for matching with only one character. Whereas, asterisk (*) matches 0 or more character.
Return the maximum value in A1:A100 when cells present in the B1:B100 starts with letter”a”:
The tilde (~) is basically an escape character which helps to find literal wildcards.
Suppose, you need to match the literal question mark (?), tilde (~), or asterisk(*), then only you need to a tilde sign in the starting of wildcard characters(i.e. ~?, ~*, ~~).
- Conditions are mainly applied by using the range/criteria in pairs.
- All the range and criteria that you assign must have the same size like Otherwise the MAXIFS function will throw the #VALUE! error.
- If none of the cells matches with the criteria then MAXIFS will give the zero (0) result.
- Automatically MAXIFS ignore all the empty cells present in the max_range which matches well with the criteria.
That’s all; I have given ample amount of detail on how to use maxifs in Excel.
So now it’s your turn to carefully follow the given instruction and grab complete benefit of this Maxifs function.
I hope you have found this article helpful and informative. Besides this if you have more knowledge on Excel Maxifs function then don’t hesitate to share it.
For any further assistance or guidance you can be in touch with us through our social media Facebook and Twitter page.