Buzz
MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )
Here are the parameters of the function:
max_range: An array of numerical values (or a range of cells containing numerical values) from which you want to return the maximum value if the criteria are met.
criteria_range1: An array of values (or a range of cells containing values) to be checked based on criteria1. (This array must have the same length as max_range).
criteria1: Condition compared against values in criteria_range1.
[criteria_range2, criteria2],
[criteria_range3, criteria3]: Other arrays of values to be checked and corresponding conditions to be evaluated.
Note:
- The Maxifs function can handle up to 126 range/criteria pairs.
- Each provided criterion can be:
+ A numerical value (integer, decimal, date, time, or logical value, such as 10, 01/01/2017, TRUE).
Or:
+ A text string (e.g., “Name”, “Wednesday”, …).
Or:
+ An expression (e.g., “> 1”, “<> 0”).
- In text-related criteria, you can use wildcard characters:
? - to match any single character.
* - to match any sequence of characters.
- If the criterion is a text string or an expression, it must be enclosed in double quotation marks when provided to the Maxifs function.
- Maxifs function is case-insensitive. For example, when comparing values in criteria_range, the text strings “TEXT” and “text” are both considered matches.
- Maxifs function was first introduced in Excel 2016, hence not available in earlier versions of Excel. Additionally, Maxifs function is not available in Excel 2016 for Mac.
Some examples of the MAXIFS function in Excel
The following spreadsheet represents sales revenue by quarter for 3 sales agents.
The Maxifs function is used to find the maximum sales revenue for quarters, regions, or salespersons (or a combination of quarters, regions, and salespersons).
This is illustrated in the examples below.
Example 1:
To find the maximum sales revenue in quarter 1, use the Maxifs function:
=MAXIFS( D2:D13, A2:A13, 1 )
Resulting in 456,000 $.
In this example, the Excel Maxifs function identifies rows with values in column A equal to 1 and returns the corresponding maximum value in column D.
The Maxifs function finds maximum values of 223,000$, 125,000$, and 456,000$ (from columns D2, D3, and D4, respectively).
Illustrative example 2:
Referencing the spreadsheet above, you can employ the Maxifs function to compute the maximum sales figure for “Jeff” in quarters 3 and 4:
=MAXIFS( D2:D13, A2:A13, '>2', C2:C13, 'Jeff' )
This formula yields the result $310,000.
In this example, the Maxifs function in Excel identifies rows with: values in column A greater than 2 and entries in column C equal to “Jeff”.
And returns the corresponding maximum value in column D.
The formula above finds the maximum value to be $310,000 and $261,000 (in cells D8 and D11).
Readers can visit the Microsoft Office website for more details on examples of the Excel Maxifs function.
Common errors related to Maxifs function
When encountering some common excel errors related to the MAXIFS function, you can handle them as follows:
Common Error:
#VALUE! - This error occurs if the max_range and criteria_range arrays provided do not have equal lengths.
#NAME? - Error occurs if you are using an older version of Excel (prior to Excel 2016) that does not support the Maxifs function.
By understanding the MAXIFS function in Excel, as well as how to use it and the related excel errors. Hopefully, you will know how to use the function more professionally, supporting your job requirements.
