How to use the MAXIFS function

What is the MAXIFS function?

The MAXIFS function returns the largest number based on a condition or criteria.

The MAXIFS function was introduced in Excel 2016. Use the MAX function and logical expressions if you have an earlier Excel version.

1. MAXIFS Function Syntax

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

You can enter up to 126 range/criteria pairs.

Back to top

2. MAXIFS Function arguments

max_range	Required. A cell reference to numbers which the highest value will be calculated.
criteria_range1	Required. The cell range you want to apply a specific condition to.
criteria1	Required. The condition you want to use.
[criteria_range2]	Optional. Additional cell ranges.
[criteria2]	Optional. Additional criteria.

Back to top

3. MAXIFS Function Example

The formula in cell F3 extracts the largest number in C3:C9 if the corresponding value on the same row in B3:B9 equals the condition specified in cell F2. Here is the data in the data table shown in cell range B2:C9 in the image above:

Region	Amount
Asia	100
Africa	10
Africa	2
Africa	4
Asia	50
Asia	65
Asia	47

The specified condition in cell F2 is "Africa". Cells B4, B5, and B6 math "Africa, the corresponding values in cell range C3:C9 are 10, 2, and 4.

Formula in cell F3:

The largest value of 10, 2, and 4 is 10 which is what the formula in cell F3 returns.

3.1 Explaining formula

The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.

Step 1 - Populate arguments

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

max_range - C3:C9
criteria_range1 - B3:B9
criteria1 - F2

Step 2 - Evaluate function

MAXIFS(B3:B9, B3:B9, F2)

becomes

MAXIFS({100; 10; 2; 4; 50; 65; 47}, {"Asia"; "Africa"; "Africa"; "Africa"; "Asia"; "Asia"; "Asia"}, "Africa")

and returns 10 in cell F3. 10 is the largest number of 10, 2, and 4.

Back to top

4. MAXIFS function - criteria

This example demonstrates the MAXIFS function using multiple conditions to extract the correct number. The data table displayed in cell range B2:D9 has the following values:

Region	Category	Amount
Asia	B	100
Africa	B	10
Africa	A	2
Africa	A	4
Asia	B	50
Asia	B	65
Asia	A	47

The formula in cell G4 in the picture above extracts the largest number (amount) that corresponds to the first condition which is region "Asia" and the second condition which is category "B". The first condition is specified in cell G2, the second condition is specified in cell G3.

Formula in cell G4:

Both conditions are met on rows 3, 7, and 8. The corresponding values are 100, 50, and 65 from column D. The largest (max) of the three values is 100.

4.1 Explaining formula

The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.

Step 1 - Populate arguments

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

max_range - B3:B9
criteria_range1 - B3:B9
criteria1 - E2
[criteria_range2] -
[criteria2]

Step 2 - Evaluate function

MAXIFS(B3:B9, B3:B9, E2)

becomes

MAXIFS({100; 10; 2; 4; 50; 65; 47}, {100; 10; 2; 4; 50; 65; 47}, "<55")

and returns 50 in cell E3.

Back to top

5. MAXIFS function - smaller than

The formula in cell E3 extracts the largest number in B3:B9 smaller than 55, condition "55" is specified in cell E2. The data table is:

Number
100
10
2
4
50
65
47

The following numbers match the condition smaller than 55: 2, 4, 47, and 50.

Formula in cell E3:

The largest value of 2, 4, 47, and 50 is 50 which is the same number the formula returns in cell E3. This example shows that you can also apply a condition to the numbers themself.

5.1 Explaining formula

The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.

Step 1 - Populate arguments

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

max_range - B3:B9
criteria_range1 - B3:B9
criteria1 - E2

Step 2 - Evaluate function

MAXIFS(B3:B9, B3:B9, E2)

becomes

MAXIFS({100; 10; 2; 4; 50; 65; 47}, {100; 10; 2; 4; 50; 65; 47}, "<55")

and returns 50 in cell E3. 50 is the largest number of 10, 2, 4, 50, and 47.

Back to top

6. How to extract the largest number ignoring error values

The MAXIFS function ignores errors if you add a criteria pair that filters values above or equal to the smallest number in your data set.

Surprisingly, this doesn't work if you use a condition like in section 3. You need to use a smaller than or larger than sign in order to ignore error values.

You can't use the IFERROR function combined with the MAXIFS function unfortunately, however, a workaround is to use the MAX function, IF function and the IFERROR function, like this: =MAX(IF(IFERROR(B3:B9,"")>=-15,IFERROR(B3:B9,""),"")) This formula must be entered as an array formula if you use an earlier Excel version than Excel 365.

Formula in cell E2:

Note that the formula in cell E2 works only with cell ranges and not arrays. The image below demonstrates this problem with array values.

Back to top

7. How to use arrays in MAXIFS function

In fact, the MAXIFS function doesn't accept anything else than cell references as max_range and criteria_range.

I recommend the IFERROR function and the regular MAX function to filter out error values.

Example array formula:

Back to top

7.1 How to enter an array formula

The image above shows leading and trailing curly brackets. They appear automatically when you follow the steps below.

1. Copy the array formula above.
2. Double press with the left mouse button on cell D3, a prompt appears.
3. Paste it to cell C3, shortcut keys are CTRL + v.
4. Press and hold CTRL + SHIFT keys simultaneously.
5. Press Enter once.
6. Release all keys.

The formula bar shows a beginning and ending curly bracket, don't enter these characters yourself.

Back to top

7.2 Explaining array formula

The Evaluate Formula tool is located on the Formulas tab in the Ribbon. It is a useful feature that allows you to step through and evaluate complex formulas to understand how the calculation is being performed and identify any errors or issues. The following steps shows these detailed evaluations for the formula above.

Step 1 - Replace error values with blanks

The IFERROR function can replace error values with a given value.

IFERROR(value, value_if_error)

IFERROR(B3:B9, "")

becomes

IFERROR({-15; #DIV/0!; 2; 4; #N/A; 65; 47}, "")

and returns

{-15; ""; 2; 4; ""; 65; 47}

Step 2 - Get the largest number

The MAX function returns the largest number ignoring text and blank values.

MAX(IFERROR(B3:B9, ""))

becomes

MAX({-15; ""; 2; 4; ""; 65; 47})

and returns 65.

Back to top

8. Can you use other functions in the MAXIFS function?

No, you can't use other functions combined with the MAXIFS function at all. The picture above shows a nested MAXIFS function. It contains an IF function in the first argument and it returns a bunch of error values in Excel 365.

This makes the MAXIFS function quite limited unfortunately.

Back to top

9. Can you perform calculations in the MAXIFS function?

No, the image above shows a dialog box containing a warning message. It appears when I tried to add 1 to the cell reference in the first argument.

The warning dialog box contains the following message:

The formula I am trying to use is:

This formula shows that you can't perform calculations inside the MAXIFS function at all, the function is very limited unfortunately.

Back to top

'MAXIFS' function examples

Functions in 'Statistical' category

The MAXIFS function function is one of 73 functions in the 'Statistical' category.

Excel function categories

Excel categories

Leave a Comment