The MAXIFS function
The biggest North order, a customer's latest visit date: the maximum, but only over the rows that meet the criteria.
Syntax
=MAXIFS(max_range, criteria_range1, criteria1, …)
In a French Excel: =MAX.SI.ENS(plage_max; plage_critères1; critères1; …).
Arguments
- max_range: the column whose maximum you want.
- criteria_range1, criteria1…: pairs of tested column and criterion.
An example
=MAXIFS(D:D,A:A,A2)
The latest date in column D for the customer in A2.
Pitfalls
- No row matches: the result is 0, not an error.
- Missing from Excel 2016 and earlier: #NAME?.
Going further
- MAX.
- Find the right formula: "the largest value by criterion".
Frequently asked questions
- And the minimum?
- MINIFS, same syntax:
=MINIFS(D:D,A:A,A2).