# 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.

#### Table of Contents

- MAXIFS function Syntax
- MAXIFS function Arguments
- MAXIFS function example
- MAXIFS function - criteria
- MAXIFS function - smaller than
- How to extract the largest number ignoring error values
- How to extract the largest number ignoring error values using arrays
- Can you use other functions in the MAXIFS function?
- Can you perform calculations in the MAXIFS function?

## 1. MAXIFS Function Syntax

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

You can enter up to 126 range/criteria pairs.

## 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. |

## 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.

## 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.

## 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.

## 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.

## 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:

### 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.

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

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

### 7.2 Explaining array formula

#### 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.

## 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.

## 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:

There's a problem with this formula.

Not trying to type a formula?

When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:

you type: =-1, cell shows: 2

To get around this, type an apostrophe (') first:

you type: '=-1, cell shows: =-1

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.

### 'MAXIFS' function examples

This article describes how to filter records based on the maximum value of a specific item. There are names in […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates formulas that lets you extract the smallest number larger than a given number. The example above specifies […]

### Functions in 'Statistical' category

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

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form