The easiest way to solve this problem is with the MAXIFS function.
However, there are actually several options.
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:C16.
If you are new to Excel Tables,this article provides an overview.
MAXIFS function
TheMAXIFS functioncan get the maximum value in a range based on one or more criteria.
The reference to E5 is relative and changes at each new row.
The TRUE values correspond to values in the table associated with group A. you’re free to instead provide an array of values created with another operation.
In this case the source data already shows groups in order, so it is not necessary.
At this point, we are ready to calculate the max values for each group.
The HSTACK function combinesugroupsandresultshorizontally, and VSTACK combines the header row and the data to make the final table.
The final resultspillsinto multiple cells on the worksheet.
Working from the inside out, theIF functionis evaluated first.
For all other groups, the value is 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.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
