In the worksheet shown, we want to sum the three largest values, sonis equal to 3.
For convenience, the range B5:C16 is anExcel Tablenameddata.
This allows the formula to usestructured references.
Note: FILTER is a newer function not available in “Legacy Excel”.
See below for an alternative formula that works in older versions of Excel.
This means we will be working through the formula from the inside out.
This is typical of Excel formulas where one function isnestedinside another.
Apply criteria
The first step in the problem is to apply criteria to select values by group.
This can be done with theFILTER function.
For this, we use the LARGE function.
TheLARGE functionis designed to return thenthlargest value in a range.
For example:
Normally, LARGE returns just one value.
However, if you supply anarray constant(e.g.
The behavior of this formula is similar to the original formula above.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
More than one condition can be tested by nesting IF functions.
The IF…
Related videos
FILTER function basic example
The SEQUENCE function
