How to use MAXIFS Function in Excel

The Excel MAXIFS function returns the largest numeric value that meets one or more criteria in a range of values. MAXIFS can be used with criteria based on dates, numbers, text, and other conditions. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

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

The MAXIFS function syntax has the following arguments:

  1. Max_range (required argument) – The actual range of cells from which the maximum value will be determined.
  2. Criteria_range (required argument) – The set of cells to be evaluated with the criteria.
  3. Criteria1 (required argument) – Here, we give a number, expression, or text that defines which cells will be evaluated as maximum.
  4. Criteria_range2 – An optional argument wherein we can specify additional ranges and their associated criteria.

Example: Let’s look at some Excel MAXIFS function examples and explore how to use the MAXIFS function as a worksheet function in Microsoft Excel:

Example 1:

MAXIFS Function - How to use MAXIFS Function in Excel

As an example, let’s find the tallest football player in our local school. Assuming the students’ heights are in cells D2:D17 (max_range) and sports are in B2:B17 (criteria_range1), use the word “football” as criteria1, and you will get this formula:

Syntax:  =MAXIFS(D2:D17;B2:B17;A21)

Result: 180

MAXIFS Function in Excel - How to use MAXIFS Function in Excel

Example 2: Find max value based on multiple criteria

Supposing, you want to find the tallest basketball player in junior school. To have it done, define the following arguments:

  • Max_range – a range of cells containing heights – D2:D17.
  • Criteria_range1 – a range of cells containing sports – B2:B17.
  • Criteria1 – “basketball”, which is input in cell G1.
  • Criteria_range2 – a range of cells defining the school type – C2:C17.
  • Criteria2 – “junior”, which is input in cell G2.
READ:  How to use PROB Function in Excel

Putting the arguments together, we get these formulas:

With “hardcoded” criteria:

Syntax: =MAXIFS(D2:D17;B2:B17;G1;C2:C17;G2)

Result: 160

MAXIFS Function 1 - How to use MAXIFS Function in Excel

Example 3:

As an extra bonus, I will show you a quick way to extract a value from another cell that is associated with the max value. In our case, that will be the name of the tallest person. For this, we will be using the classic INDEX MATCH formula and nest MAXIFS in the first argument of MATCH as the lookup value:

Syntax: =INDEX(A2:A17;MATCH(MAXIFS(D2:D17;B2:B17;G1;C2:C17;G2);D2:D17;0))

Result:The formula tells us that the name of the tallest basketball player in junior school is Jack Albertson

MAXIFS Function 3 - How to use MAXIFS Function in Excel

Example 4:

To see how it works in practice, let’s add the Age column (column C) to our sample table and find the maximum height among the students aged between 13 and 14. This can be done with the following criteria:

Criteria1: “>=13”

Criteria2: “<=14”

Because we compare the numbers in the same column, criteria_range in both cases is the same (C2:C17):

Syntax: =MAXIFS(D2:D17; C2:C17; “>=13”;C2:C17; “<=14”)

Result: 180

MAXIFS Function 5 - How to use MAXIFS Function in Excel

Aside from numbers, logical operators can also work with text criteria. In particular, the “not equal to” operator comes in handy when you wish to exclude something from your calculations. For example, to find the tallest student in all sports excluding volleyball, use the following formula:

Syntax: =MAXIFS(D2:D11;B2:B11; “<>”&B20)

Result: 179

MAXIFS Function in Excel 1 - How to use MAXIFS Function in Excel

Example 5: MAXIFS formulas with wildcard characters (partial match)

To evaluate a condition that contains a specific text or character, include one of the following wildcards in your criteria:

  • Question mark (?) to match any single character.
  • Asterisk (*) to match any sequence of characters.
READ:  How to use QUARTILE.INC Function in Excel

For this example, let’s find out the tallest student in game sports. Because the names of all game sports in our dataset end with the word “ball”, we include this word in the criteria and use an asterisk to match any previous characters:

Syntax: =MAXIFS(D2:D17;B2:B17;”*”&B24)

Result: 182

MAXIFS Function in Excel 2 - How to use MAXIFS Function in Excel

Note:

  1. #VALUE! error – This is returned when the size and shape of the max_range and criteria_rangeN arguments aren’t the same.
  2. If we are using earlier versions of Excel, we can use an array formula based on MAX and IF to find maximum values with criteria.
  3. #NAME? error – Occurs when we are using an older function of Excel.
  4. MAXIFS will include rows that are hidden.

 

Add a Comment

Your email address will not be published. Required fields are marked *