In other words, we want the max value after applying multiple criteria.
The easiest way to solve this problem is with the MAXIFS function.
However, if you need more flexibility (i.e.
Each approach is explained below.
Excel Table
For convenience, all data is in anExcel Tablenameddatain the range B5:D16.
If you are new to Excel Tables,this article provides an overview.
All conditions must be true in order for value to be considered.
In other words, MAXIFS will not treat empty cells that meet criteria as zero.
On the other hand, MAXIFSwillreturn zero (0) if no cells match criteria.
If it’s crucial that you use arrays, see the MAX + FILTER option below.
Theincludeargument is a simple expression:
This is an example of usingBoolean logicin Excel.
you’re free to instead provide anarrayof values created with another operation.
This is important when source data needs to be manipulated before a max value is calculated.
Working from the inside out, theIF functionis evaluated first.
Only values associated with group “A” and a temperature less than 73 make it through the filter.
The other values are replaced with FALSE.
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
More than one condition can be tested by nesting IF functions.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
