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
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 74 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 signs
Use 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 Oscar
You can contact me through this contact form