A to Z of Excel Functions: the MAXIFS Function
24 January 2022
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the MAXIFS function.
The MAXIFS function
The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria. It has the following syntax:
MAXIFS(max_range, criterion_range1, criterion1, [criterion_range2, criterion2], ...)
- max_range is the actual range of cells in which the maximum is to be determined
- criterion_range1 is the set of cells to evaluate with the criterion specified
- criterion1 is the criterion in the form of a number, expression or text that defines which cells will be evaluated as a maximum
- criterion_range2 (onwards) and criterion2 (onwards) are the additional ranges and their associated criteria. 126 range / criterion pairs may be specified. All ranges must have the same dimensions otherwise the function returns an #VALUE! error.
It should be noted that:
- the size and shape of the max_range and criteria_rangeN arguments must be the same, otherwise these functions return the #VALUE! error.
As an example:
This example is preferable to its standard Excel counterpart:
Array formulae are cumbersome and not readily understood, which is why MAXIFS may be a highly viable alternative.
We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.
A full page of the function articles can be found here.