# How to use 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.

Formula in cell F3:

### 3.1 Explaining formula

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

The formula in cell G4 in the picture above extracts the largest number (amount) that corresponds to region Asia and category B.

Formula in cell G4:

Only three values match 100, 50, and 65. The largest of the three values is 100.

### 4.1 Explaining formula

#### 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, specified in cell E2.

Formula in cell G4:

### 5.1 Explaining formula

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

Formula in cell E2:

Note that this 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, 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.

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

### 'MAXIFS' Function examples

The following 4 articles have formulas containing the MAXIFS function.

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 how to extract the smallest number larger than a condition and the largest number smaller than a […]

This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

### Functions in 'Statistical' category

The MAXIFS function function is one of many 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