This tutorial shows how to calculate Large with criteria in Excel using the example below;
To return the largest values in a set of data with criteria, you can use the a formula based on the LARGE and IF functions.
In the example shown, the formula in F5 is:
Note: this is an array formula and must be entered with control + shift + enter.
How this formula works
The LARGE function can be used to retrieve “nth” largest value in numeric data like so:
In this example, we need include only values associated with group B. To do this, we use the IF function to filter:
Since we are running a logical test on an range of cells, we get an array of results:
Note that only values in group B make it into the array. Group A values become FALSE since they fail the logical test. This array is returned inside the LARGE function with 2 hardcoded as as “nth” (the argument “k” in LARGE):
LARGE then returns 95, the second largest value in group B as the final result.
To take into account multiple criteria, you can extend the formula with boolean logic in a form like this:
Where criteria1 and criteria2 and represent an expression to test values in a criteria range, as shown in the original example above.