where data is an Excel Table in the range B5:C16 and n is 3. The result is the sum of quantity for the first 3 Red values.
Example formula
In the example shown, the formula in cell G5, copied down, is: Notice the value for n is hardcoded as 3. This formula is a good example of nesting one function inside another.
Extracting matching data
Working from the inside out, the first task is to extract a list of quantities by color. This is done with the FILTER function like this: With “Red” in cell F5, the result is an array that contains quantities associated with “Red”: Notice there are 6 numbers in this array, one for each entry where the color is Red.
Extract first 3 values
The next task is to extract just the first 3 values from the array returned by FILTER. This is done with the TAKE function. FILTER returns the array directly to the TAKE function as the array argument, with the rows argument hardcoded as 3: The TAKE function then returns the first 3 values in the array:
Sum results
The last step in the problem is to sum the results from FILTER and TAKE. This is done with the SUM function. TAKE returns the first 3 values to SUM: And SUM returns 17 as a final result. This is the sum of the first 3 quantities for “Red”. When the formula is copied down to cell G6, we get a sum of the first 3 “Blue” quantities.
Sum last n matching values
To sum the last n matching values, simply change n to a negative number like this: The TAKE function is explained in more detail here.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
