Uncover the Secrets: How to Use MAXIFS in Excel?

In Microsoft Excel, MAXIFS is an excellent function that helps to find the largest value in the range that meets specific criteria. However, it’s beneficial when you want to filter your spreadsheet data based on multiple conditions & then detect the maximum value within that filtered subset. Now the question arises, how to use MAXIFS in Excel …?

In this helpful guide, you will learn each and everything that you need to know about the MAXIFS function. So, let’s get started…

What Is Maxifs Formula?

Excel MAXIFS functions basically return the biggest numeric value that matches one or more criteria in the range of values.

MAXIFS is used with criteria based on the text, numbers, dates, and other conditions.

Objective to use: 

To get the maximum value using the criteria

Return value 

Maximum value

Syntax 

=MAXIFS (max_range, range1, criteria1, [range2], [criteria2], …)

Arguments 

• max_range– Range of values is used for determining the maximum result.
• range1– 1^st range to evaluate.
• criteria1– criteria for using the range1.
• Range2– [optional] 2nd range to evaluate.
• Criteria2– [optional] criteria for using the range2.

Version 

Excel 2019, Excel 365

How to Use MAXIFS In Excel?

Maxif function returns the largest numeric value that matches 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 match with the condition assigned by the criteria1.

If you want to add some more conditions then apply it using the criteria/range pairs.

2^nd 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 divide the logical criteria into two main parts i.e, criteria + range.

Due to this, the syntax which is used for making criteria is quite different. MAXIFS needsa  cell range for the range arguments. So you are not allowed to use an array.

MAXIFS is a newly added function in the Excel application; thus, it is available only in Excel 2019 and 365 versions. 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

Excel 365:

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.

Excel 2019:

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 1^st formula, MAXIFS gives the maximum value within D5:D16, whereas C5:C16 is equal to “F” (93).

In 2^nd formula, MAXIFS returns the maximum value in D5:D1,6, whereas C5:C16 is equal to “M” (83).

In the given example, the MAXIFS function is used with 2 criteria. One e 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

Other criteria

To get the maximum value in the A1:A100 when the cells in B1:B100 are more than 50.

=MAXIFS(A1:A100,B1:B100,”>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.

=MAXIFS(A1:A100,B1:B100,”<=100″,C1:C100,”>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”.

=MAXIFS(A1:A100,B1:B100,”<>red”)

Value From Another Cell

If you are using the 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.

=MAXIFS(A1:A100,B1:B100,”>”&C1)

You need to only keep the sign of the greater operator (>) enclosed within the quotes (“”).

Wildcards

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.

For example,

Return the maximum value in A1:A100 when cells present in the B1:B100 starts with letter”a”:

=MAXIFS(A1:A100,B1:B100,”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 you only need to use a tilde sign at the start of wildcard characters(i.e. ~?, ~*, ~~).

Notes

• Conditions are mainly applied by using the range/criteria in pairs.
• All the ranges and criteria that you assign must have the same size otherwise the MAXIFS function will throw the #VALUE! error.
• If none of the cells match with the criteria, then MAXIFS will give a zero (0) result.
• Automatically, MAXIFS ignores all the empty cells present in the max_range that match well with the criteria.

Related FAQs:

What Can I Use Instead of Maxifs in Excel?

No, there is no such MAXIF function in the older version of Excel application. Excel only offers COUNTIF and SUMIF function. In order to make your own MAXIF just use the IF and MAX functions together in the array formula.

How Does Maxifs Work in Excel?

The MAXIFS function in Excel returns the determined value among the cells specified by a given set of the conditions or criteria.

Why is Maxifs Returning 0?

The max_range holds only non-numeric values. If the cells range in which you need to locate the maximum value has only non-numeric values, the function will immediately return zero.

Does Excel Have a Maxif Function?

In Excel latest versions like Excel 365 and Excel 2019, this Maxifs Function is available. In the previous version of Excel, you need to use the array formula mainly based on the IF and MAX to extract maximum values using the criteria.

What Is the Formula for Maxifs Array?

The syntax you can use for the MAXIFS function is: MAXIFS(max_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

Closure

That’s all; I have given ample information on how to use maxifs in Excel. So, now it’s your turn to carefully follow the given instruction and get the complete benefit of this 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 & Twitter pages.

Good Luck…!