How to Use MAXIFS and MINIFS in Excel?
Max and Min with Conditions is a Excel function that returns the maximum or minimum value from a specified range based on one or more criteria.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Discover how to efficiently find maximum or minimum values that meet specific criteria in your data sets using Excel functions.
The Formula
"Find the maximum or minimum value in a range that meets one or more conditions, using MAXIFS and MINIFS"
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) or =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)This formula returns the maximum or minimum value from a specified range based on one or more criteria.
Step-by-Step Breakdown
- Identify the range from which to find the maximum or minimum value.
- Specify the criteria range that will be evaluated against the conditions.
- Define the condition(s) that must be met for the values to be considered.
- Optionally, add more criteria ranges and conditions as needed.
Edge Cases & Warnings
- No values meet the criteria, resulting in an error or zero.
- All values in the range are the same, returning that value.
- Criteria ranges of different sizes can lead to errors.
- Using non-numeric data in a numeric context may cause unexpected results.
Examples
"MAXIFS(B2:B10, A2:A10, 'Sales', C2:C10, '>100')"
Returns the maximum sales value greater than 100."MINIFS(D2:D10, E2:E10, 'Active', F2:F10, '<50')"
Returns the minimum value from active entries less than 50.Frequently Asked Questions
Can I use MAXIFS and MINIFS with text criteria?
Yes, you can use text criteria to filter the data.
What happens if my criteria ranges are not the same size?
Excel will return an error if the criteria ranges do not match in size.
Are MAXIFS and MINIFS available in all versions of Excel?
These functions are available in Excel 2016 and later versions.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.