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.
Formula in cell F3:
=MAXIFS(C3:C9, B3:B9, F2)
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.
Back to top
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:
=MAXIFS(D3:D9, B3:B9, G2,C3:C9, G3)
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.
Back to top
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:
=MAXIFS(B3:B9, B3:B9, E2)
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.
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.
Formula in cell E2:
=MAXIFS(B3:B9, B3:B9, ">=-15")
Note that this 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:
=MAX(IFERROR(B3:B9, ""))
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.
- 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.
Back to top
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.
Back to top
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.
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.
Back to top
'MAXIFS' function examples
The following 4 articles contain the MAXIFS function.
Functions in this article
Functions in 'Statistical' category
The MAXIFS function function is one of many functions in the 'Statistical' category.
Calculates the average of the absolute deviations of data points from their mean.
Calculates the average of numbers in a cell range.
Returns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to 1.
Returns the average of cell values that are valid for a given condition.
Returns the average of cell values that evaluates to TRUE for multiple criteria.
Calculates the beta distribution.
Calculates the inverse of the cumulative beta distribution.
Calculates the individual term binomial distribution probability.
Calculates the minimum value for which the binomial distribution is equal to or greater than a given threshold value.
Calculates the probability of the chi-squared distribution, cumulative distribution or probability density.
Calculates the right-tailed probability of the chi-squared distribution.
Calculates the inverse of the left-tailed probability of the chi-squared distribution.
Calculates the inverse of the right-tailed probability of the chi-squared distribution.
Calculates the test for independence, the value returned from the chi-squared statistical distribution and the correct degrees of freedom. Use this function to check if hypothesized results are valid.
Calculates the confidence interval for a population mean.
Calculates the confidence range for a population mean using a Student's t distribution.
Calculates the correlation between two groups of numbers.
Counts all numerical values in an argument.
Counts the non-empty or non-blank cells in a cell range.
Counts empty or blank cells in a range.
Calculates the number of cells that is equal to a condition.
Calculates the number of cells across multiple ranges that equals all given conditions.
Calculates the covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the sample covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the exponential distribution representing an outcome in the form of probability.
Calculates the F probability for two tests.
Calculates the right-tailed F probability for two tests.
Calculates the two-tailed probability from an F-test
Calculates a value based on existing x and y values using linear regression.
Calculates how often values occur within a range of values and then returns a vertical array of numbers.
Calculates the GAMMA value.
Calculates the gamma often used in queuing analysis (probability statistics) that may have a skewed distribution.
Calculates the geometric mean.
Returns estimated exponential growth based on given data.
Returns a value representing the y-value where a line intersects the y-axis.
Calculates the k-th largest value from an array of numbers.
Returns an array of values representing the parameters of a straight line based on the "least squares" method.
Returns an array of values representing the parameters of an exponential curve that fits your data, based on the "least squares" method.
Calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev.
Calculate the largest number in a cell range.
Calculates the highest value based on a condition or criteria.
Calculates the median based on a group of numbers. The median is the middle number of a group of numbers.
Returns the smallest number in a cell range.
Returns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE to 0 (zero).
Calculates the smallest value based on a given set of criteria.
Returns the most frequent number in a cell range. It will return multiple numbers if they are equally frequent.
Calculates the most frequent value in an array or range of data.
Calculates the normal distribution for a given mean and standard deviation.
Calculates the inverse of the normal cumulative distribution for a given mean and standard deviation.
Calculates the percent rank of a given number in a data set.
Calculates the percent rank of a given number compared to the whole data set.
Returns the number of permutations for a set of elements that can be selected from a larger number of elements.
Returns the number of permutations for a specific number of elements that can be selected from a larger group of elements.
Calculates a number of the density function for a standard normal distribution.
Calculates the probability that values in a range are between a given lower and upper limit.
Returns the quartile of a data set.
Returns the quartile of a data set, based on percentile values from 0..1, inclusive.
Returns the rank of a number out of a list of numbers.
Calculates the rank of a number in a list of numbers, based on its position if the list were sorted.
Calculates the skewness of a group of values.
Calculates the slope of the linear regression line through coordinates.
Returns the k-th smallest value from a group of numbers.
Calculates a normalized value from a distribution characterized by mean and standard_dev.
Returns standard deviation based on the entire population.
Returns standard deviation based on a sample of the entire population.
Estimates the standard deviation from a sample of values.
Returns the standard deviation based on the entire population, including text and logical values.
Calculates values along a linear trend.
Calculates the mean of the interior of a data set.
Returns the variance based on the entire population. The function ignores logical and text values.
The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and text values.
Excel function categories
Excel functions that let you resize, combine, and shape arrays.
Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.
Perform basic operations to a database-like structure.
Functions that let you perform calculations to Excel date and time values.
Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.
Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.
Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.
Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.
These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.
You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.
Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.
Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.
Get data from the internet, extract data from an XML string and more.
Excel categories
Latest updated articles.
More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
More than 1300 formulas organized in subcategories.
Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
An Excel feature that lets you visualize data in a graph.
Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
UDF stands for User Defined Functions and is custom built functions anyone can create.
A list of all published articles.
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