Maximum value by condition (MAXIF)

Description

In this example, we will consider a formula that will help us find the maximum value by condition. In the standard Excel set, there are only simple functions to find the maximum MAX or minimum MIN values.

To write this formula, we will need to use two standard functions: MAX and IF. We will nest the IF() function inside MAX() and enter it as an array formula. For this in the cell D15 enter:

=MAX(IF($B$2:$B$11=B15;$D$2:$D$11))

and press Ctrl+Shift+Enter at the same time (this is how the array formula is entered). Then, it remains only to stretch our formula down.

Visually, the result will look like this:

We see that the formula is enclosed in curly brackets ({ }) , this is a sign that an array formula is entered.

Similarly, we can make a formula for the MIN function:

=MIN(IF($B$2:$B$11=B15;$D$2:$D$11))