Excel MAXIFS Function
The Excel MAXIFS function returns the largest numeric value that meets one or more criteria in a range of values. MAXIFS can be used with criteria based on dates, numbers, text, and other conditions. MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
- max_range - Range of values used to determine maximum.
- range1 - The first range to evaluate.
- criteria1 - The criteria to use on range1.
- range2 - [optional] The second range to evaluate.
- criteria2 - [optional] The criteria to use on range2.
The MAXIFS function can be used to find a maximum value in data after applying one or more criteria. Conditions are applied using range/criteria pairs. MAXIFS can handle up to 126 range/criteria pairs. Each criteria range supplied must be the same size as the max_range.
MAXIFS will automatically ignore empty cells, even when criteria match. In other words, MAXIFS will not treat empty cells that meet criteria as zero. On the other hand, MAXIFS will return zero (0) if no cells match criteria.
MAXIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and MAXIFS requires a cell range for range arguments, you can't use an array.
In the example shown, the formula in G7 is:
MAXIFS returns the maximum value in D3:D15 where C3:C15 is equal to "F".
- Criteria range(s) must be the same size as the max_range.
- MAXIFS will return a #VALUE error if a criteria range is not the same size as the min_range.