Enrollment for the FMVA™ Certification Program is now OPEN!

MAXIFS Function in Excel

Get the maximum value based on specified criteria

What is the MAXIFS Function?

The MAXIFS Function in Excel is a Statistical function that returns the maximum value based on one or more than one conditions from the specified cells. The MAXIFS function was introduced in MS Excel 2016.

 

Formula

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

 

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 will 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 the additional ranges and their associated criteria.

MS Excel allows putting in up to 126 criteria.

 

How to use the MAXIFS Function in Excel?

It is a built-in function that can be used as a worksheet function in Excel. Let’s consider a few examples:

 

Example 1

Suppose we are given the quarterly expenses from the three regions in which our business operates. We wish to find out the maximum expense for the North region, so let’s see the formula to be used:

=MAXIFS( $D$5:$D$33, B$5:B$33, “North”)

 

MAXIFS Function

 

In the example above, the Excel MAXIFS function identifies rows where the value in column B is equal to North, and returns the maximum value from the corresponding values in column D.

 

MAXIFS Function - Example 1

 

In this example, the MAXIFS Function identifies rows where the value in column A is equal to North and returns the maximum value from the corresponding values in column C.

 

Example 2

Let’s continue with the same example. We wish to know which region registered the highest printing and stationary expense. The formula to use will be:

=MAXIFS( $D$5:$D$33, C$5:C$33, “Printing & Stationary” )

The formula will return the amount with the expense of Printing & stationary.

 

MAXIFS Function - Example 2

 

Example 3

Let’s take an example. We wish to find out the student who scored Distinction in any subject and the marks scored are maximum. We use the data below:

 

MAXIFS Function - Example 3

 

The formula to use will be =MAXIFS(B6:B16,C6:C16,”A”,E6:E16,”=Distinction”)

 

MAXIFS Function - Example 3a

 

In criteria_range1, C10, C11, and C16 match the criteria of “A” of the corresponding cells in criteria_range2, E10, E11 and E16 match the criteria of Distinction. Finally, of the corresponding cells in max_range, B11 gives the maximum value. The result is therefore 50.

 

MAXIFS Function - Example 3b

 

Example 4

Let’s take this example wherein we would use MAXIFS with an array formula. We wish to use “MAXIFS” formula that will return the latest date for open tasks for each project in Column E for the data below:

 

MAXIFS Function - Example 4

 

So, for the data example above, we would expect to see 01/03/2017 in Column F on all rows for Project A; 01/05/2017 for all rows of Project B; and 01/01/2017 for all rows of Project C.

The formula to be used is below:

 

MAXIFS Function - Example 4a

 

For parentheses, we need to press Ctrl+Shift+Enter the first time we input it because it only works as an array formula. After that, we can drag it over the range where you need it.

 

MAXIFS Function - Example 4b

 

Things to remember about the MAXIFS function

  1. #VALUE! error – It is returned when 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 minimum values with criteria.
  3. #NAME? error – Occurs when we are using an older function of Excel.
  4. MAXIFS will include rows that are hidden.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!